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]

What is the name of the worksheet in the workbook GlobalData where the find is to be done?
Is there a header in place for column N of the sheet in MyData where you want to paste the data?
Will the workbook GlobalData be open when you run the macro?

 

[rznfcc]

The worksheet in GlobalData is named "Regional Data Input"
The Heading name in both the GlobalData and MyData workbook is =[@[RtOP ID]]
Finally, yes, the GlobalData workbook will assumed to be open when the macro is run.

For what it is worth, the names of the column headings that correspond to range of data that needs to be copied are:
[@[Report date (DD-MONTH-YY)]]:[@[Probable Root Cause]]

The column headings are identical between the copied data of the source and destination workbooks. The Destination workbook has several additional columns at the front (Cols A:M) which have additional tracking info not available in the GlobalData workbook.

 

[JoeMo]

Try this (untested) on a copy of your workbook. The code goes into a standard module in the MyData workbook and should be run from that workbook with the sheet you want the data returned to active. The workbook GlobalData must be open. Change the file extension for GlobalData in the code where noted by a comment to whatever it is (I assumed it is .xlsx). I'm sure you know that if you are using Excel 2007 or a later version, the MyData workbook must be saved as a macro-enabled workbook (file extension .xlsm) and macros must be enabled to run the code.

Sub rznfcc()
Dim wS As Workbook, sS As Worksheet, sD As Worksheet
Dim nR As Long, F As Variant, fR As Range, vA As Variant
Set wS = Workbooks("GlobaData.xlsx") 'Change file extension to suit
Set sS = wS.Sheets("Regional Data Input")
Set sD = ActiveSheet
On Error Resume Next
F = Application.InputBox("Use your mouse to select a cell w/Unique ID", Type:=8)
On Error GoTo 0
If F Is Nothing Then Exit Sub
With sS.Columns("C:C")
    On Error Resume Next
    Set fR = .Find(F, .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"))
        With sD
            nR = .Range("N" & Rows.Count).End(xlUp).Row + 1
            .Range("N" & nR).Resize(1, UBound(vA, 2)).Value = vA
        End With
    Else
        MsgBox "Unique ID " & F & " not found in column C of GlobalData worksheet " & sS.Name
        Exit Sub
    End If
End With
End Sub

 

[JoeMo]

Slight revision.

Sub rznfcc()
Dim wS As Workbook, sS As Worksheet, sD As Worksheet
Dim nR As Long, F As Range, fR As Range, vA As Variant
Set wS = Workbooks("GlobaData.xlsx") 'Change file extension to suit
Set sS = wS.Sheets("Regional Data Input")
Set sD = ActiveSheet
On Error Resume Next
Set F = Application.InputBox("Use your mouse to select a cell w/Unique ID", Type:=8)
On Error GoTo 0
If F Is Nothing Then Exit Sub
With sS.Columns("C:C")
    On Error Resume Next
    Set fR = .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"))
        With sD
            nR = .Range("N" & Rows.Count).End(xlUp).Row + 1
            .Range("N" & nR).Resize(1, UBound(vA, 2)).Value = vA
        End With
    Else
        MsgBox "Unique ID " & F.Value & " not found in column C of GlobalData worksheet " & sS.Name
        Exit Sub
    End If
End With
End Sub

 

[rznfcc]

Joe,
Thank you for your help!

I've copied the vba into my "MyData" workbook and only made minor change (updated file name & extension) and saved. Both MyData and GlobalData were opened at the time and a cell was selected in "MyData" which contained the Unique ID I wanted to update. MyData has scripts enabled.

I stepped into the macro and when I reached the statement noted below in BOLD, i received a "Runtime error '9': Subscript out of range".

Sub rznfcc()
Dim wS As Workbook, sS As Worksheet, sD As Worksheet
Dim nR As Long, F As Range, fR As Range, vA As Variant
Set wS = Workbooks("AMS_Global_Consolidated_Report.xlsm") 'Change file extension to suit
Set sS = wS.Sheets("Regional Data Input")
Set sD = ActiveSheet

For clarity sake, these are the actual names of files/worksheets in use. It doesn't seem like this would make a difference, but who knows?

MyData = "Unix 2012 PbM Tracking.xlsm"
MyData Worksheet = "MidrangeData"
GlobalData = "AMS_Global_Consolidated_Report.xlsm"
GlobalData Worksheet = "Regional Data Input"

I looked up the error code but it seems to be a pretty generic error.

 

[JoeMo]

Joe,
Thank you for your help!

I've copied the vba into my "MyData" workbook and only made minor change (updated file name & extension) and saved. Both MyData and GlobalData were opened at the time and a cell was selected in "MyData" which contained the Unique ID I wanted to update. MyData has scripts enabled.

I stepped into the macro and when I reached the statement noted below in BOLD, i received a "Runtime error '9': Subscript out of range".

Sub rznfcc()
Dim wS As Workbook, sS As Worksheet, sD As Worksheet
Dim nR As Long, F As Range, fR As Range, vA As Variant
Set wS = Workbooks("AMS_Global_Consolidated_Report.xlsm") 'Change file extension to suit
Set sS = wS.Sheets("Regional Data Input")
Set sD = ActiveSheet

For clarity sake, these are the actual names of files/worksheets in use. It doesn't seem like this would make a difference, but who knows?

MyData = "Unix 2012 PbM Tracking.xlsm"
MyData Worksheet = "MidrangeData"
GlobalData = "AMS_Global_Consolidated_Report.xlsm"
GlobalData Worksheet = "Regional Data Input"

I looked up the error code but it seems to be a pretty generic error.

I created a workbook with the name AMS_Global_Consolidated_Report.xlsm and ran the code I posted through the offending line. I cannot reproduce the error. Are you certain the workbook AMS_Global_Consolidated_Report.xlsm is open in the same instance of Excel as the workbook containing the code? Are you certain the name of the workbook is exactly what you placed in the code? To test, in any empty cell of the global workbook enter this formula then copy the cell and paste special values back to it.
=CELL("filename",A1)
The name of the workbook will be in square brackets like this:
C:\Users\Joe\Desktop\[AMS_Global_Consolidated_Report.xlsm]Sheet2.

 

[rznfcc]

Sorry for delaying in responding ... I had a couple days of leader workshops and couldn't get back to this until this morning.

Interestingly, the error identified earlier has disappeared. I am certain that both files were open at the time but that is irrelevant now since I am able to step further through the script now but it is still erroring...just at a different spot.

Now, I get an error after prompted to select the field. I get
"Run-time error '1004': Application-defined or object-defined error"
If Not fR Is Nothing Then
vA = .Range(Cells(fR.Row, "A"), Cells(fR.Row, "BC"))

Below is a step-by-step process that I follow when I do this manually. It's pretty simple so I can't see where this part could cause the problem.
1. Open Destination workbook --> "Unix 2012 PbM Tracking.xlsm" (true name for MyData) / "MidrangeData" worksheet. "Enable Content".
2. Open Source workbook -->"AMS_Global_Consolidated_Report.xlsm" (true name for GlobalData) / "Regional Data Input" worksheet. "Enable Content"
3. I switch to the "MidrangeData" worksheet
4. Run the script.
5. Dialog box requests I select the cell with the Unique ID
6. I click on a cell in col "P" of "Unix 2012 PbM Tracking.xlsm" (true name for MyData) / "MidrangeData" worksheet and press "OK". The dialog shows "$P$311" which matches the cell I selected and contains the value "37207" (the value I need to find in -->"AMS_Global_Consolidated_Report.xlsm" (true name for GlobalData) / "Regional Data Input" worksheet.
7. I get "Run-time error '1004': Application-defined or object-defined error"

 

[JoeMo]

Sorry for delaying in responding ... I had a couple days of leader workshops and couldn't get back to this until this morning.

Interestingly, the error identified earlier has disappeared. I am certain that both files were open at the time but that is irrelevant now since I am able to step further through the script now but it is still erroring...just at a different spot.

Now, I get an error after prompted to select the field. I get
"Run-time error '1004': Application-defined or object-defined error"
If Not fR Is Nothing Then
vA = .Range(Cells(fR.Row, "A"), Cells(fR.Row, "BC"))

Below is a step-by-step process that I follow when I do this manually. It's pretty simple so I can't see where this part could cause the problem.
1. Open Destination workbook --> "Unix 2012 PbM Tracking.xlsm" (true name for MyData) / "MidrangeData" worksheet. "Enable Content".
2. Open Source workbook -->"AMS_Global_Consolidated_Report.xlsm" (true name for GlobalData) / "Regional Data Input" worksheet. "Enable Content"
3. I switch to the "MidrangeData" worksheet
4. Run the script.
5. Dialog box requests I select the cell with the Unique ID
6. I click on a cell in col "P" of "Unix 2012 PbM Tracking.xlsm" (true name for MyData) / "MidrangeData" worksheet and press "OK". The dialog shows "$P$311" which matches the cell I selected and contains the value "37207" (the value I need to find in -->"AMS_Global_Consolidated_Report.xlsm" (true name for GlobalData) / "Regional Data Input" worksheet.
7. I get "Run-time error '1004': Application-defined or object-defined error"

insert a msgbox (in red below):

If F Is Nothing Then Exit Sub
MsgBox F.Value
With sS.Columns("C:C")

run the code again - do you get the value of the cell you selected at the prompt?

 

[rznfcc]

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