Two sheets, find data in Sheet 2 based on matching Cell from Sheet 1....

[FatalLordes]

Hey all

Ok, I'm new to Excel and VBA so I'm having trouble even working out how to go about this. I have two workbooks, BookA and BookB. Both have a column which would match (Col1). What I need BookA to do is, for each row of data, look at the column that would have the matching data (Col1) and find the matching data in BookB (which also happens to be in Col1). It then needs to copy the content of Col5 for that row in Book B and paste it into the corresponding row in BookA but in Col4. Make sense?

For example, BookA looks like the following:

Ref	Title	Date	Topic	Size	Colour
1	Big Banana	09/02/2021	Bananas	Big	Yellow
2	Little Apple	09/02/2021	Apples	Little	Red

BookB looks like the following:

Ref	Title	Date	Version	Topic	Size	Colour	Shape	Author
1	Big Banana	09/02/2021	5	Bananas in all their glory	Big	Yellow	Circle	BW
2	Little Apple	09/02/2021	2	Apples	Little	Red	Square	AB

So in BookA it would look in the Ref column and find the first row data ("1") and then go look up BookB to find the matching Ref, then copy Col 5 from BookB for that row and paste it into Col 4 for that row in BookA. So "Bananas" would update to become "Bananas in all their glory" once the script has run. Does that make sense? And it needs to do that for every row in BookA. If possible, if it doesn't find a match in BookB (very rare but I guess not impossible), I would like to shade the Ref Cel in BookA so it is obvious something was wrong, or something like that, but that is a nice to have... the main thing is getting that data from BookB to BookA.

Hope that makes sense And hope someone can point me in the right direction as to how to even go about this. Truly appreciate all your help :D

 

[mohadin]

Hi
When you say Booka & BookB, That's means 2 files OR same file and two sheets?
Any way
Try this code for one workbook 2 sheets!!

Sub test()
    Dim i As Long
    Dim ad As Range
    Dim ref As String
    Dim sh1 As Worksheet: Dim sh2 As Worksheet
    Set sh1 = Sheets("Sheet1"): Set sh2 = Sheets("Sheet2")
    For i = 2 To sh1.Cells(Rows.Count, 1).End(xlUp).Row
        ref = sh1.Cells(i, 1).Value
        Set ad = sh2.Columns("A:A").Find(What:=ref, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True)
        If ad Is Nothing Then
            sh1.Cells(i, 1).Interior.Color = vbRed
        Else
            sh1.Cells(i, 1).Offset(, 3) = sh2.Range(ad.Address).Offset(, 4).Resize(, 4).Value
        End If
    Next
End Sub

 

[FatalLordes]

Hi
When you say Booka & BookB, That's means 2 files OR same file and two sheets?
Any way
Try this code for one workbook 2 sheets!!

Sub test()
    Dim i As Long
    Dim ad As Range
    Dim ref As String
    Dim sh1 As Worksheet: Dim sh2 As Worksheet
    Set sh1 = Sheets("Sheet1"): Set sh2 = Sheets("Sheet2")
    For i = 2 To sh1.Cells(Rows.Count, 1).End(xlUp).Row
        ref = sh1.Cells(i, 1).Value
        Set ad = sh2.Columns("A:A").Find(What:=ref, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True)
        If ad Is Nothing Then
            sh1.Cells(i, 1).Interior.Color = vbRed
        Else
            sh1.Cells(i, 1).Offset(, 3) = sh2.Range(ad.Address).Offset(, 4).Resize(, 4).Value
        End If
    Next
End Sub

It is two separate workbooks, not one workbook two sheets. That’s why I’m not sure how or where to start. Lol. But thanks for you help and guidance.

 

[mohadin]

Then try this
Assuming Boo1&book2 are both open the the code in Book1
Book1>>sheet1
book2>>sheet2

Sub test()
    Dim i As Long
    Dim ad As Range
    Dim ref As String
    Dim sh1 As Worksheet: Dim sh2 As Worksheet
    Set sh1 = Sheets("Sheet1")
    Application.ScreenUpdating = False
    For i = 2 To sh1.Cells(Rows.Count, 1).End(xlUp).Row
        ref = sh1.Cells(i, 1).Value
        Windows("Book2").Activate
        Set sh2 = ActiveWorkbook.Sheets("sheet2")
        Set ad = sh2.Columns("A:A").Find(What:=ref, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True)
        If ad Is Nothing Then
            sh1.Cells(i, 1).Interior.Color = vbRed
        Else
            sh1.Cells(i, 1).Offset(, 3) = sh2.Range(ad.Address).Offset(, 4).Resize(, 4).Value
        End If
    Next
    Windows("Book1").Activate
    Application.ScreenUpdating = True
End Sub

 

[FatalLordes]

Dim i As Long Dim ad As Range Dim ref As String Dim sh1 As Worksheet: Dim sh2 As Worksheet Set sh1 = Sheets("Sheet1") Application.ScreenUpdating = False For i = 2 To sh1.Cells(Rows.Count, 1).End(xlUp).Row ref = sh1.Cells(i, 1).Value Windows("Book2").Activate Set sh2 = ActiveWorkbook.Sheets("sheet2") Set ad = sh2.Columns("A:A").Find(What:=ref, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True) If ad Is Nothing Then sh1.Cells(i, 1).Interior.Color = vbRed Else sh1.Cells(i, 1).Offset(, 3) = sh2.Range(ad.Address).Offset(, 4).Resize(, 4).Value End If Next Windows("Book1").Activate Application.ScreenUpdating = True

Ok, I will give that a try. Do I have to define the two worksheets somewhere? I thought I would because how does it know which is which? But I will try it and see how I go Thanks again.

 

[FatalLordes]

Then try this
Assuming Boo1&book2 are both open the the code in Book1
Book1>>sheet1
book2>>sheet2

Sub test()
    Dim i As Long
    Dim ad As Range
    Dim ref As String
    Dim sh1 As Worksheet: Dim sh2 As Worksheet
    Set sh1 = Sheets("Sheet1")
    Application.ScreenUpdating = False
    For i = 2 To sh1.Cells(Rows.Count, 1).End(xlUp).Row
        ref = sh1.Cells(i, 1).Value
        Windows("Book2").Activate
        Set sh2 = ActiveWorkbook.Sheets("sheet2")
        Set ad = sh2.Columns("A:A").Find(What:=ref, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True)
        If ad Is Nothing Then
            sh1.Cells(i, 1).Interior.Color = vbRed
        Else
            sh1.Cells(i, 1).Offset(, 3) = sh2.Range(ad.Address).Offset(, 4).Resize(, 4).Value
        End If
    Next
    Windows("Book1").Activate
    Application.ScreenUpdating = True
End Sub

Ok, I'm obviously missing something. I've added your code and I've ensured both workbooks are open before running. I've amended your code to include the relevant book and sheet names where I think are needed because otherwise, from what I understand, it would have no idea or their names, but I'm obviously missing something as I get a "Runtime error 9; subscript out of range" for the line saying "Set sh1 = Sheets("Sheet1")" (and yes the sheet is called Sheet1 in the Book1.xlsx. Book 2, where it needs to pull the data from to overwrite in book1, is called "source_master" and the sheet is called Report. Oh, also in Book2, the column to get the data from is col10 now and it needs to paste into Book1 in Col7, hence why I changed them (hoping I got them around the right way but I cant test due to the error).

What am I missing???

Dim i As Long
Dim ad As Range
Dim ref As String
Dim sh1 As Worksheet: Dim sh2 As Worksheet
Set sh1 = Sheets("Sheet1")
Application.ScreenUpdating = False
For i = 2 To sh1.Cells(Rows.Count, 1).End(xlUp).Row
ref = sh1.Cells(i, 1).Value
Windows("source_master.xls").Activate
Set sh2 = ActiveWorkbook.Sheets("Report")
Set ad = sh2.Columns("A:A").Find(What:=ref, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True)
If ad Is Nothing Then
sh1.Cells(i, 1).Interior.Color = vbRed
Else
sh1.Cells(i, 1).Offset(, 7) = sh2.Range(ad.Address).Offset(, 10).Resize(, 10).Value
End If
Next
Windows("Book1.xlsx").Activate
Application.ScreenUpdating = True

 

[FatalLordes]

Ok, I'm obviously missing something. I've added your code and I've ensured both workbooks are open before running. I've amended your code to include the relevant book and sheet names where I think are needed because otherwise, from what I understand, it would have no idea or their names, but I'm obviously missing something as I get a "Runtime error 9; subscript out of range" for the line saying "Set sh1 = Sheets("Sheet1")" (and yes the sheet is called Sheet1 in the Book1.xlsx. Book 2, where it needs to pull the data from to overwrite in book1, is called "source_master" and the sheet is called Report. Oh, also in Book2, the column to get the data from is col10 now and it needs to paste into Book1 in Col7, hence why I changed them (hoping I got them around the right way but I cant test due to the error).

What am I missing???

Dim i As Long
Dim ad As Range
Dim ref As String
Dim sh1 As Worksheet: Dim sh2 As Worksheet
Set sh1 = Sheets("Sheet1")
Application.ScreenUpdating = False
For i = 2 To sh1.Cells(Rows.Count, 1).End(xlUp).Row
ref = sh1.Cells(i, 1).Value
Windows("source_master.xls").Activate
Set sh2 = ActiveWorkbook.Sheets("Report")
Set ad = sh2.Columns("A:A").Find(What:=ref, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True)
If ad Is Nothing Then
sh1.Cells(i, 1).Interior.Color = vbRed
Else
sh1.Cells(i, 1).Offset(, 7) = sh2.Range(ad.Address).Offset(, 10).Resize(, 10).Value
End If
Next
Windows("Book1.xlsx").Activate
Application.ScreenUpdating = True

I worked it out! The issue was that Book1 wasn't the active sheet. So I inserted a line right at the start to say "Windows("Book1.xlsx").Activate" and now it works! Hazzah! And I think I follow and understand your code (as much as I can) as I got the right columns and named the sheets/books correctly.

Thank you SO much for your help and teaching. I've learnt a lot from this!

 

[FatalLordes]

I worked it out! The issue was that Book1 wasn't the active sheet. So I inserted a line right at the start to say "Windows("Book1.xlsx").Activate" and now it works! Hazzah! And I think I follow and understand your code (as much as I can) as I got the right columns and named the sheets/books correctly.

Thank you SO much for your help and teaching. I've learnt a lot from this!

Oh I think I spoke too soon. I thought it was copying from Book2 to Book1 but it isn't. It is highlighting if it can't find a match, which is good, but it isn't actually copying anything from Book2 to Book1???

 

[FatalLordes]

Then try this
Assuming Boo1&book2 are both open the the code in Book1
Book1>>sheet1
book2>>sheet2

Sub test()
    Dim i As Long
    Dim ad As Range
    Dim ref As String
    Dim sh1 As Worksheet: Dim sh2 As Worksheet
    Set sh1 = Sheets("Sheet1")
    Application.ScreenUpdating = False
    For i = 2 To sh1.Cells(Rows.Count, 1).End(xlUp).Row
        ref = sh1.Cells(i, 1).Value
        Windows("Book2").Activate
        Set sh2 = ActiveWorkbook.Sheets("sheet2")
        Set ad = sh2.Columns("A:A").Find(What:=ref, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True)
        If ad Is Nothing Then
            sh1.Cells(i, 1).Interior.Color = vbRed
        Else
            sh1.Cells(i, 1).Offset(, 3) = sh2.Range(ad.Address).Offset(, 4).Resize(, 4).Value
        End If
    Next
    Windows("Book1").Activate
    Application.ScreenUpdating = True
End Sub

Hi mohadin

I've tried your code, adding in the top line to ensure Book 1 is the active book other I got errors, but it isn't copying the data from Book2 (source_master, where its sheet is called Report) to Book1. It is highlighting if it can't find it, mind you you, so that is a win. It needs to copy the entire content of Col10 in Book 2 to Col7 in Book1. What have I stuffed up?

    Windows("Book1.xlsx").Activate

    Dim i As Long
    Dim ad As Range
    Dim ref As String
    Dim sh1 As Worksheet: Dim sh2 As Worksheet
    Set sh1 = Sheets("Sheet1")
    Application.ScreenUpdating = False
    For i = 1 To sh1.Cells(Rows.Count, 1).End(xlUp).Row
        ref = sh1.Cells(i, 1).Value
        Windows("source_master.xls").Activate
        Set sh2 = ActiveWorkbook.Sheets("Report")
        Set ad = sh2.Columns("A:A").Find(What:=ref, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True)
        If ad Is Nothing Then
            sh1.Cells(i, 1).Interior.Color = vbRed
        Else
            sh1.Cells(i, 1).Offset(, 7) = sh2.Range(ad.Address).Offset(, 10).Resize(, 10).Value
        End If
    Next
    Windows("Book1.xlsx").Activate
    Application.ScreenUpdating = True

 

[FatalLordes]

I worked it out! The offset was off for each book by 1. I'm not really understanding why. I wanted to copy Col10 but I had to say the offset was 9 and I wanted to copy to Col7 but I had to say offset 6. I don't really understand why that is but once I changed them it worked!