Copy Different Ranges in Local Workbook, Paste & Transpose in another Workbook on Office 365

[foratoms]

Hi,

Please I am new to VBA. I have two worksheets. One on my laptop (and workmates. clones) and the other on office 365 Onedrive. when my colleagues fill their respective workbooks each on their systems and click a submit button, the data in the selected range is pasted in the Onedrive worksheet (in a transposed and rearranged form. So the desired user can just print or export as PDF and send to the concerned officer.

Source Worksheet A: name ""Menu-BEtest.xlsm" Sheet Name "MenuCenter"

I want the source data in C1, F1, C5:G6 and C9:G10 copied in the destination as shown below and then the details filled in the range above cleared, then finally a pop up that "Your Booking has been successfully recorded!"

My problem is how to specify the rearrangement as shown below

DESTINATION: Name "Tracking Spreadsheet.xlsx" Worksheet name "data"

My code:

1 Option Explicit
2. Private Sub cmdAdd_Click()
3 'Change Workbook
4 Dim iRow As Long
5 Dim cwb As Workbook ' cloud workbook
6 Dim dwb As Workbook  ' Desktop Workbook
7 Dim wsCopy As Worksheet
8 Dim wsDest As Worksheet
9 Dim lCopyLastRow As Long
10 Dim lDestLastRow As Long
11 Set wsCopy = Workbooks("MeNU.BEtest.xlsm").Worksheets("MenuCenter")
12 Set wsDest = Workbooks("Tracking Spreadsheet.xlsx").Worksheets("Data")
13 Set cwb = Workbooks.Open("[URL]https://iheightplc.my.sharepoint.com/personal/...../Tracking[/URL] Spreadsheet.xlsx")
14    'Determine emptyRow
15        iRow = WorksheetFunction.CountA(cwb.Sheets("data").Range("A:A")) + 1
16        'lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
17.With cwb.Sheets("data")
18  'wsCopy.Range("C23:G" & lCopyLastRow).Copy _
19  'wsDest.Range("A" & lDestLastRow)
20 End With
21 ActiveWorkbook.Save
22 ActiveWindow.Close
23   MsgBox "Congratulations " & Application.UserName & _
24           vbNewLine & "Your Booking has been successfully recorded!", _
25            vbInformation, "Success!"
 26        Range("C25:G26").ClearContents
 27 Range("C29:G30").ClearContents
28          End Sub

The destination table is opened and i get this error with no data pasted and the destination folder remaining open instead of closing

How will the code be able to, in logical order;
1. properly open the destination worksheet,
2 paste in desired order,
3 close the destination,
4 pop up message and
5.then clear the source range.

Please assist me