VBA copy matched cells to other sheet

July 19, 2019 at 01:18:23
Specs: Windows 10
Hi everybody,

I have two excel sheets, and I'm trying to match a specific column in sheet1 and sheet2 and copy-paste the matched information from sheet2 to sheet1. I have an on-going working code display below, and the problem is the code only matches, copy-paste the content once, but there are many repetitive cells in a column, thus resulting in missing cells when pasting. Any idea how to solve this?

Code:
Sub Button2_Click()
Dim lastRw1, lastRw2, nxtRw, m

lastRw1 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
lastRw2 = Sheets(2).Range("B" & Rows.Count).End(xlUp).Row
'Loop
For nxtRw = 2 To lastRw2
'Search
With Sheets(1).Range("A2:A" & lastRw1)
Set m = .Find(Sheets(2).Range("B" & nxtRw), LookIn:=xlValues, lookat:=xlWhole)
'Copy
If Not m Is Nothing Then
Sheets(2).Range("C" & nxtRw & ":D" & nxtRw).Copy _
Destination:=Sheets(1).Range("J" & m.Row)
End If
End With
Next
End Sub

message edited by Mark.T


See More: VBA copy matched cells to other sheet

Reply ↓  Report •

#1
July 19, 2019 at 03:41:27
First, a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link.

Thanks!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
July 20, 2019 at 14:05:01
Without seeing your data, it's a little confusing as to what you are trying to do.

What do you mean by "there are many repetitive cells in a column"?

What column? The Sheet1 "search for this" column or the Sheet2 "search here" column? Or both?

Perhaps a short example of your data and the expected outcome would help.

Once again, please click on the following link and read the instructions on how to post VBA code and example data in this forum.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •
Related Solutions


Ask Question