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

FatalLordes

New Member
Joined
Dec 22, 2017
Messages
11
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:
RefTitleDateTopicSizeColour
1Big Banana09/02/2021BananasBigYellow
2Little Apple09/02/2021ApplesLittleRed

BookB looks like the following:
RefTitleDateVersionTopicSizeColourShapeAuthor
1Big Banana09/02/20215Bananas in all their gloryBigYellowCircleBW
2Little Apple09/02/20212ApplesLittleRedSquareAB

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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
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!!
VBA Code:
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

New Member
Joined
Dec 22, 2017
Messages
11
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!!
VBA Code:
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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Then try this
Assuming Boo1&book2 are both open the the code in Book1
Book1>>sheet1
book2>>sheet2
VBA Code:
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

New Member
Joined
Dec 22, 2017
Messages
11

ADVERTISEMENT

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

New Member
Joined
Dec 22, 2017
Messages
11
Then try this
Assuming Boo1&book2 are both open the the code in Book1
Book1>>sheet1
book2>>sheet2
VBA Code:
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

New Member
Joined
Dec 22, 2017
Messages
11

ADVERTISEMENT

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

New Member
Joined
Dec 22, 2017
Messages
11
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

New Member
Joined
Dec 22, 2017
Messages
11
Then try this
Assuming Boo1&book2 are both open the the code in Book1
Book1>>sheet1
book2>>sheet2
VBA Code:
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?

VBA Code:
    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

New Member
Joined
Dec 22, 2017
Messages
11
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! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,769
Messages
5,638,246
Members
417,016
Latest member
Tegguy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top