Hi The below code is working for me, however on the first Loop in Column A when I paste it overwrites the column heading, but every column after that it pastes the data below the heading. Why is the first column different?
Is the Paste section of my code incorrect? Can it be done better? Its the code under 'Paste header that is causing the trouble?
Dim LookupTab As Worksheet
Set LookupTab = ThisWorkbook.Sheets("Lookups")
LookupTab.Select
LookupTab.Range("A2", Range("A" & Rows.Count).End(xlUp)).Name = "Dynamic"
UserForm1.lstHeaders.RowSource = "Dynamic"
Dim LocationTab As Worksheet
Set LocationTab = ThisWorkbook.Sheets("Location")
Dim RawDataTab As Worksheet
Set RawDataTab = ThisWorkbook.Sheets("Raw_Data")
Dim x As Integer
With UserForm1.lstHeaders
For x = 0 To .ListCount - 1
Header = UserForm1.lstHeaders.List(x, 0)
On Error Resume Next
'Copy Header
LookupCopyHeader = Application.WorksheetFunction.Match(Header, RawDataTab.Rows(1), 0)
RawDataTab.Select
With RawDataTab
.Range(.Cells(3, LookupCopyHeader), .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, LookupCopyHeader)).Copy
End With
'Paste Header
LookupPasteHeader = Application.WorksheetFunction.Match(Header, LocationTab.Rows(1), 0)
LocationTab.Select
With LocationTab
.Range(.Cells(2, LookupCopyHeader), .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, LookupCopyHeader)).PasteSpecial
End With
Next x
End With
Bookmarks