Find, Copy, Paste a part of a data row between Excel workbooks

[rznfcc]

Hi all,
I am very new to macro and VBA writing and see that there are a lot of very talented folks on this board who may be able to help.

I have 2 workbooks I work with daily. One workbook (call it GlobalData) contains global data where any of 50+ fields could have changed each day. The second workbook (call it MyData) contains just those data records that my organization needs to track. I don't have any control over GlobalData but MyData can be modified as needed.

Today, I have to:
1) manually copy a Unique ID from MyData (col P),
2) (F)ind that unique ID in GlobalData (col C)
3) Copy the data from cols A:BC for the record found
4) (P)aste-Special (Values) the data back into Mydata (beginning in col N).

This activity only takes 15 minutes but it has to be done every day.

Can a macro be written to do this? I figured that I would select the unique key in MyData and run the macro to update that record automatically from GlobalData.

 

[JoeMo]

The Msgbox displayed "37207" which corresponds to the contents of the cell I had selected.

Change this line:
vA = .Range(Cells(fR.Row, "A"), Cells(fR.Row, "BC"))
to this
vA = sS.Range(Cells(fR.Row, "A"), Cells(fR.Row, "BC"))

 

[rznfcc]

Thank you again for you perseverance!

Unfortunately, a new error is presented: Run-time error 1004: Method 'Range' of object '_Worksheet' failed.

 

[JoeMo]

Thank you again for you perseverance!

Unfortunately, a new error is presented: Run-time error 1004: Method 'Range' of object '_Worksheet' failed.

Try this replacement:

With sS
    On Error Resume Next
    Set fR = .Columns("C:C").Find(F.Value, .Cells(1, 1), xlValues, xlWhole, xlByRows, xlNext)
    On Error GoTo 0
    If Not fR Is Nothing Then
        vA = .Range(Cells(fR.Row, "A"), Cells(fR.Row, "BC"))

beginning at the line:
With SS.Columns("C:C")