I want to copy all the rows and columns in multiple worksheets in a one workbook to a single worksheet in a different workbook. In addition, I just want to copy the header once, even though it is in all of the worksheets I'll copy.
I can open the workbook containing all of the worksheets I want to copy to my destination worksheet/workbook however, I don't know how to copy the header only once and often get a Paste Special error.
Sub Raw_Report_Import()
'Define variables'
Dim ws As Worksheet
Dim wsDest As Worksheet
'Set target destination'
Set wsDest = Sheets("Touchdown")
'For loop to copy all data except headers'
For Each ws In ActiveWorkbook.Sheets
'Ensure worksheet name and destination tab do not have same name'
If ws.Name <> wsDest.Name Then
ws.Range("A2", ws.Range("A2").End(xlToRight).End(xlDown)).Copy
wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
Next ws
End Sub
Expected: All of the target worksheets from second workbook are copied and pasted to destination worksheet "Touchdown" in first workbook and the header is copied only once.
Actual: Some values are paste but the formatting is wrong from what they were and it is not lining up correctly.