VBA to find match, then copy and paste a range

[ebcnt]

I have what I think is a fairly simple process I need to perform, but haven't been able to find the VBA code in my searches.

I would like to create a macro that will do the following:
I have 2 workbooks (WB1 and WB2). Both workbooks will have a variable amount of entries in column A. I would like the macro to loop through column A of WB1 and search for a match for that cell value in column A of WB2. If it finds a match, it will copy cells BU:CA from the row that contains the matching value in WB2 and use it to replace those same cells in WB1. If it does not find a matching value, I would like it to leave cells BU:CA of WB1 unchanged.

Thank you so much.

 

[stanleydgromjr]

ebcnt,

Post a screenshot of your sheet(s) with Colo's HTML Maker.

[url]http://www.puremis.net/excel/downloads.shtml
[/url]


Have a great day,
Stan

 

[Peter_SSs]

ebcnt

Welcome to the MrExcel board!

Can you just confirm that we are dealing with two workbooks (ie 2 files) and not two worksheets (tabs) within the same workbook?

If two workbooks, can you tell us the actual names (maybe they are WB1.xls and WB2.xls) and what are the sheet names that contain the data in each workbook?

If two worksheets within the same workbook, then can you tell us the names of those two worksheets?

Also, can you confirm that if the value is found in WB2 col A, that it will only occur once in that column? If it can occur more than once, what action should be taken?

 

[ebcnt]

To answer your questions:
Yes we are dealing with two different workbooks.
I am using a tab named "PRS" in the workbook named "Product Requests MO.xls" (this is WB1 in my initial description).
The other is a tab named "New PRS" in a workbook named "206pr.xls". This would be WB2 in my example.
The value will only occur one time in WB2 column A.
Thanks very much.

 

[Peter_SSs]

Assuming both workbooks open and headings in row 1, try this:

Sub FindAndCopy()
  Dim WS1 As Worksheet
  Dim WS2 As Worksheet
  Dim Rng1 As Range
  Dim Rng2 As Range
  Dim c As Range
  
  Application.ScreenUpdating = False
  Set WS1 = Workbooks("Product Requests MO.xls").Sheets("PRS")
  Set WS2 = Workbooks("206pr.xls").Sheets("New PRS")
  Set Rng1 = WS1.Range(WS1.Range("A2"), WS1.Range("A" & Rows.Count).End(xlUp))
  Set Rng2 = WS2.Range(WS2.Range("A2"), WS2.Range("A" & Rows.Count).End(xlUp))
  For Each c In Rng1
    On Error Resume Next
    Rng2.Find(What:=c).Offset(, 72).Resize(, 7).Copy Destination:=c.Offset(, 72)
    Err.Clear
  Next c
  Set WS1 = Nothing
  Set WS2 = Nothing
  Set Rng1 = Nothing
  Set Rng2 = Nothing
  Application.ScreenUpdating = True
End Sub

 

[ckgeary]

This code is just what I needed! Thanks for the post What if I wanted to do this same function within just one workbook? (the file is a .xlms)

 

[Abhi88]

I want to know is it possible to copy duplicate values also using this code and if not, what modifications should be done? Thank you​

​

 

[Peter_SSs]

I want to know is it possible to copy duplicate values also using this code and if not, what modifications should be done? Thank you​

​

Your question is not clear to me. In any case I'm sure your book/sheet names data locations or something else will be different from the original question so you need to make it clear what your have, where and what you are trying to achieve.

 

[Abhi88]

I have done all the changes and there is no problem with the current code. I am able to copy what I want but only thing is that, it is copying only the first occurrence. Let me give one demo. In the following snapshot, A1 & A2 are duplicates. When I use your code, it is only copying the first occurrence (i.e. A1) but not A2.

 

[Peter_SSs]

I have done all the changes and there is no problem with the current code.

1. Could you post it? (See my signature block below for how to post formatted code with Code Tags)
2. Your circumstances are different to the original so we need to know about the differences. For example, the original code pasted the found values 72+ columns to the right of the cell being searched for. If we did that for multiple matches they would all get pasted over the top of each other. So we at least need to know where you would want any extra values placed.