Creating Macro to Auto-Populate Data from one Workbook to another

Question

To provide some background, I have looked around Stack to try and find a solution to my problem but have come up short.

Using VBA to compare two excel workbooks

excel vba macro to match cells from two different workbooks and copy and paste accordingly

What I am trying to do is identify if the same numbers are presented in two workbooks, "Workbook A" and "Workbook B". If the same data is present in both workbooks, I want to have data from another column, in Workbook B, to auto-populate in a certain column in Workbook A.

The caveat about Workbook B is that the document will live on a SharePoint site.

The column the data lives in Workbook A is B. The same data the column lives in Workbook B is C.

If the numbers are the same in both workbooks, I want to copy the data from column M in Workbook B and put it in column J in Workbook A.

Any help would be greatly appreciated.

This is the only code I have been able to find:

I am not highly skilled with VBA and don't have any code that works yet. The closest code I was able to find is below:

Sub UpdateW2()

Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, FR As Long

Application.ScreenUpdating = False

Set w1 = Workbooks("4130 Sedgwick Activity Report 03212016_GT.xlsb").Worksheets("4130 Lincoln")
Set w2 = Workbooks("Test_Eaton Absence Detail Report 04-15-2016 (2)_GT.xlsx").Worksheets("Active Locations")


For Each c In w1.Range("B3", w1.Range("J" & Rows.Count).End(xlUp))
    FR = 0
    On Error Resume Next
    FR = Application.Match(c, w2.Columns("A"), 0)
    On Error GoTo 0
    If FR <> 0 Then w2.Range("M" & FR).Value = c.Offset(, -3)
Next c
Application.ScreenUpdating = True
End Sub

Please see [how to ask](http://stackoverflow.com/help/how-to-ask). — findwindow, Apr 21 '16 at 15:26
Pretty difficult to help with this. Your problem is too broad - what is it you can't do? — OldUgly, Apr 22 '16 at 05:06
I pasted the code that I was able to find in my original question above. — Glenn, Apr 22 '16 at 12:38
@OldUgly I am trying to compare two workbooks. If workbook A has the same data located in workbook B, I want to take data from another column in workbook B and populate it in a column in workbook A. — Glenn, Apr 22 '16 at 17:56

score 0 · Answer 1 · answered Apr 22 '16 at 21:14

In the code you posted, there is a combination of two lines that will cause a problem.

This line ...

For Each c In w1.Range("B3", w1.Range("J" & Rows.Count).End(xlUp))

... says to loop through all of the cells in the range "B3" through "J"something. "B" is the second column in the worksheet.

This line ...

If FR <> 0 Then w2.Range("M" & FR).Value = c.Offset(, -3)

... says to take the value three columns backwards from the cell the loop is looking at and put it into column "M" of the worksheet in the other workbook. If you go three columns backward from column "B", you will get an error.

In your comments, you only say "I want to take data from another column in workbook B", but you don't say which column.

If the "another column" is really three columns back, then you will at least need to change this line ...

For Each c In w1.Range("B3", w1.Range("J" & Rows.Count).End(xlUp))

... to this line ...

For Each c In w1.Range("D3", w1.Range("J" & Rows.Count).End(xlUp))

If the "another column" is always fixed (e.g. Column A), then you could change this line ...

 If FR <> 0 Then w2.Range("M" & FR).Value = c.Offset(, -3)

... to this line ...

If FR <> 0 Then w2.Range("M" & FR).Value = w1.Range("A" & c.Row)

One of these changes should get you code that "works".

Creating Macro to Auto-Populate Data from one Workbook to another

1 Answers1