Hello,
I have a code that I wrote to copy data from one worksheet to another in the same workbook based on matching column header names between the two worksheets. My source data has 14 columns and my reconciled ('target") data has 10 columns. Between the two sets of data, there are eight columns total that match in terms of column header names. However, for some reason, my code is only matching two columns out of the possible eight between these two worksheets.
Below is my code and attached is my sample workbook. Any help is much appreciated!
[CODE]Sub TestData()
Dim shSource As Worksheet, shReconcile As Worksheet
Dim SourceLastCol As Long, SourceLastRow As Long, SourceRow As Range
Dim Found1 As Range, Found2 As Range, j As Long, HeaderNameMatch As String
Set shSource = Worksheets("SourceData")
Set shReconcile = Worksheets("ReconData")
With shSource
SourceLastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
For j = 1 To SourceLastCol
HeaderNameMatch = .Cells(1, j).Value
Set SourceRow = .Range("A1", .Cells(1, SourceLastCol))
Set Found1 = SourceRow.Find(What:=HeaderNameMatch, lookat:=xlWhole, MatchCase:=False)
If Not Found1 Is Nothing Then
SourceLastCol = shReconcile.Cells(1, Columns.Count).End(xlToLeft).Column
Set SourceRow = shReconcile.Range("A1", shReconcile.Cells(1, SourceLastCol))
Set Found2 = SourceRow.Find(What:=HeaderNameMatch, lookat:=xlWhole, MatchCase:=False)
If Not Found2 Is Nothing Then
SourceLastRow = .Cells(Rows.Count, Found1.Column).End(xlUp).Row
.Range(.Cells(2, Found1.Column), .Cells(SourceLastRow, Found1.Column)).Copy
Found2.Offset(1, 0).PasteSpecial xlPasteAll
End If
End If
Next j
End With
End Sub[CODE]
Bookmarks