So I have two sheets. One is full of data with headers in row 1 (Sheet1) and the other has headers in row 9 (Sheet2). I need to copy the data from Sheet1 with a certain header and put it in Sheet2 with the corresponding header. For example, if the header in Sheet1 is "bob", I want to copy all of the data in the column headed with "bob" and place it in a hard-coded column in Sheet2.
My code currently filters through the headers in Sheet1 and when it selects the one I need to copy to the other sheet, it copies the entire column and pastes it into Sheet2 in column I. This column will always be the same, but I'd like the information to paste into I9 rather than at I1. I found some code online that helped with some modification.
However, I can't do this because copying the entire column creates issues when I try to paste, an error 1004. So I can only paste into I1, otherwise there is an overflow. How can I change my copy method so that only cells in the column with header "bob" that are filled are copied, and then pastes?
Code is below:
Dim ws As Worksheet
Dim aCell As Range, Rng As Range
Dim col As Long, lRow As Long
Dim colName As String
Dim i As Integer
'activate temp worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
Set res = ThisWorkbook.Sheets("Sheet2")
With ws
Set a = .Range("A1:ZZ1").Find(What:="bob", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False)
'if found
If Not a Is Nothing Then
'copy column
a.EntireColumn.Copy
'insert column at I9
Cells("I9").PasteSpecial Paste:=xlPasteValues
Else
MsgBox "Parameter (bob) not found in (Sheet1)"
End If
End With
Thank you!