Selectively copying cells based on column header

[jfsknight]

Lectori Salutem (Greetings Reader),

If this is explained in another thread please direct me there. I searched unsuccessfully.

Here's what I'm trying to do (with very little VBA experience):
I'm trying to copy cells from a column in one workbook to cells in a column in another workbook based on what's in a header column.

Such as

Original workbook
Mat Qty
A 1
C 3
D 2
E 4
G 5
Total 15

Target workbook becomes
Mat Qty -> Mat Qty
A -> A 1
B -> B
C -> C 3
D -> D 2
E -> E 4
F -> F
G -> G 5

My code so far (which might not be worth reading) is:

Sub CopyColumnToWorkbook()
Dim sourceColumn As Range, targetColumn As Range, sourceColumnValue As Range, targetColumnValue As Range
Dim mySourceCell As Range, myTargetCell As Range

Set sourceColumn = Workbooks("1100 Finished Goods on Hand 2013-01.xlsx").Worksheets("Pivot").Columns("A")
Set sourceColumnValue = Workbooks("1100 Finished Goods on Hand 2013-01.xlsx").Worksheets("Pivot").Columns("B")
Set targetColumn = Workbooks("1100 Finished Goods on Hand.xlsm").Worksheets("1100-Production").Columns("B")
Set targetColumnValue = Workbooks("1100 Finished Goods on Hand.xlsm").Worksheets("1100-Production").Columns("C")

For Each mySourceCell In sourceColumn.Cells
    For Each myTargetCell In targetColumn.Cells
        If mySourceCell.Value = myTargetCell.Value Then
           sourceColumnValue(mySourceCell.Row, mySourceCell.Column).Copy Destination:=targetColumnValue(myTargetCell.Row, myTargetCell.Column)
        End If
    Next
Next

End Sub

I'm getting a runtime error ('1004'. Application-defined or object-defined error) on the Copy statement within the If sttement. What am I doing wrong? I don't mind throwing out all of this code for new.

Thank you,

 

[Jeffrey Lopez]

I am curious as to why you are not just using a VLOOKUP to pull the information over?

 

[mwrohr]

Untested but this may help:

Sub Test()
Dim r1 As Range, src As Range, dest As Range

Set r = Sheet1.Range("A1")
Set src = Sheet1.Range("A1").EntireRow.Find("Column Header", , , xlWhole)
Set dest = Sheet1.Range("A1").EntireRow.Find("Column Header", , , xlWhole)
Set dest = dest.Offset(1, 0)

While r <> "Total"
    src.Copy dest

    Set r = r.Offset(1, 0)
    Set src = src.Offset(1, 0)
    Set dest = dest.Offset(1, 0)
Wend

End Sub

 

[jfsknight]

Of course VLOOKUP works splendidly. I'll just use that then copy the values over themselves.
Thanks.

The code will help me learn VBA so not a wasted endeavor.