VBA to find match, then copy and paste a range

E

ebcnt

New Member
Joined
Apr 24, 2007
Messages
3
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
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?
 
Upvote 0
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.
 
Upvote 0
Assuming both workbooks open and headings in row 1, try this:

VBA Code: 
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
 
Last edited:
Upvote 0
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)
 
Upvote 0
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​





​
 
Upvote 0
Abhi88 said:
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​





​
Click to expand...
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.
 
Upvote 0
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.




uJxQO.jpg
 
Upvote 0
Abhi88 said:
I have done all the changes and there is no problem with the current code.
Click to expand...
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
VBA Workbook Syntax
Replies
8
Views
109
hayden
H
R
  • Question
Excel Data Pull
Replies
8
Views
259
RTAssocia
R
B
  • Locked
  • Question
Find and Replace WB references (VBA)
Replies
7
Views
653
RoryA
RoryA
G
  • Question
Copying and pasting between two workbooks
Replies
1
Views
518
rlv01
R
N
  • Question
VBA - Copying specific data to open workbook.
Replies
1
Views
323
eiloken
E

Forum statistics

Threads
1,215,324
Messages
6,124,249
Members
449,149
Latest member
mwdbActuary

Share this page

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
Back
Top