VBA: Find (match) from a list, then copy and paste into different sheet

blonde · Mar 14, 2018

Hi,

I’m wondering if someone could help me please as I’m struggling to get this working. A button click runs a sub containing two with statements. The first one (with ws) works fine. This finds certain records from the main list in worksheet ws, it copies selected columns and pastes the results into worksheet ws2.

I’m having trouble with the second with statement (with ws2). It should look at range F in sheet ws2 (Finalrow2), which has been newly populated under the first with statement (with ws). This column contains unique record values. Using range F, it should find the matching record on worksheet ws3, matching on column E in ws3. From this it should copy a range from the record row in ws3 and then paste into worksheet ws2 from column H.

I’m currently getting an error on this line:

Code:

Set pop_fin = ws3.Range("E7:E1000").Find(i, , , xlWhole, , , False, , False)

I’d be very grateful for your help on this.

Here is my code:

Code:

Private Sub CommandButton1_Click()
 
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim pop_fin As String
 
Set ws = ThisWorkbook.Sheets("2017-18 student list")
Set ws2 = ThisWorkbook.Sheets("Interim")
Set ws3 = ThisWorkbook.Sheets("QLS 17-18 Download")
 
Finalrow = Sheets("2017-18 student list").Range("A902").End(xlUp).Row
Finalrow2 = Sheets("Interim").Range("F902").End(xlUp).Row
Finalrow3 = Sheets("QLS 17-18 Download").Range("A902").End(xlUp).Row
 
Application.ScreenUpdating = False
 
Sheets("Interim").Range("A2:N902").ClearContents
 
With ws
   
    For i = 7 To Finalrow
   
    If .Cells(i, 1).Value = "Erasmus+ Study" Or _
        .Cells(i, 1).Value = "Erasmus+ Work" Then
                   
    Intersect(.Range("A:F, AO:AO"), .Rows(i)).Copy
    Sheets("Interim").Range("A902").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
       
    End If
    Next i
 
End With
 
With ws2
       
    For i = 2 To Finalrow2
 
    Set pop_fin = ws3.Range("E7:E902").Find(i, , , xlWhole, , , False, , False)
  
    If Not pop_fin Is Nothing Then
  
    Intersect(.Range("F:N"), .Rows(i)).Copy
    ws2.Range("A902").End(xlUp).Offset(1, 8).PasteSpecial xlPasteValues
  
     End If
     Next i
 
End With
 
Application.ScreenUpdating = True
 
End Sub

Fluff · Mar 14, 2018

Try

Code:

Set pop_fin = Ws3.Range("E7:E902").Find(.Range("F" & i).Value, , , xlWhole, , , False, , False)

blonde · Mar 14, 2018

Hi, thank you very much for helping with the code. I’ve got that working now (I needed to change the dim from a string to a range as well for it to run). However, it has flushed out a new error on the next line. This is copying from the active worksheet (ws2) instead of (ws3). I am having difficulty getting to grips with the syntax again:

Code:

  Intersect(.Range("F:N"), .Rows(i)).Copy

Please could you advise on this? I’ve tried the following but I’m getting a type mismatch:

Code:

  Intersect(ws3, Range("F:N"), .Rows(i)).Copy

Fluff · Mar 14, 2018

Try

Code:

 Intersect(ws3.Range("F:N"), ws3.Rows(i)).Copy

blonde · Mar 14, 2018

Hi, thank you for advising on that line, it now works. However, I can see now that I’ve got the whole with statement mixed up as it is copying and pasting between the worksheets, but just not doing it correctly!

It is copying from row 2 in ws3 instead of starting from row 7 in ws3. I changed ‘For i = 2 to Finalrow2’ to ‘For i = 7 to Finalrow2’ to see what difference that would make and it then copied from row 7 in ws3 which didn’t make sense to me as Finalrow2 is on ws2.

It is not pasting the range against the correct record row in ws2. Instead I just get one single range pasted on the next row down at the end of the list. My brain is fried over this, so I would greatly appreciate further help please to help sort this out.

Here is the code:

Code:

With ws2    
   For i = 2 To Finalrow2
   Set pop_fin = ws3.Range("E7:E1000").Find(.Range("F" & i).Value, , , xlWhole, , , False, , False)
   If Not pop_fin Is Nothing Then
        Intersect(ws3.Range("F:N"), ws3.Rows(i)).Copy
        ws2.Range("A901").End(xlUp).Offset(1, 8).PasteSpecial xlPasteValues 
   End If  
   Next i  
End With

Fluff · Mar 14, 2018

Ok, change this

Code:

Intersect(ws3.Range("F:N"), ws3.Rows(i)).Copy

to

Code:

pop_fin.Offset(, 1).Resize(, 9).Copy

Where should it be copied too?

blonde · Mar 14, 2018

It should be copied to column H in ws2, thank you.

Fluff · Mar 14, 2018

Which row?
First blank row, or row i?

blonde · Mar 14, 2018

Sorry, it should be row i.

Fluff · Mar 14, 2018

Change

Code:

ws2.Range("A901").End(xlUp).Offset(1, 8).PasteSpecial xlPasteValues

to

Code:

ws2.Range("H"& i).PasteSpecial xlPasteValues

VBA: Find (match) from a list, then copy and paste into different sheet

blonde

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

blonde

New Member

Fluff

MrExcel MVP, Moderator

blonde

New Member

Fluff

MrExcel MVP, Moderator

blonde

New Member

Fluff

MrExcel MVP, Moderator

blonde

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

VBA: Find (match) from a list, then copy and paste into different sheet

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock