Copy and past with matching

Question

I need to use a macro that will copy and paste an entire row based on whether the text of a cell matches another. I looked for something similar on the site but was not able to find something that could help. I'll outline the process I am trying to do:

Copy and paste a list of program names (the number of names can vary) from one sheet to another. (this one I have already completed)
Check each program name (number of programs can vary) individually to see if it matches a separate list on a separate sheet.
If it matches, copy and paste the entire row, if it doesn't, move to the next.

I tried using if and then statements, but I was having issues trying to loop it (if thats the correct term). The size of the list can vary, so making sure that this is taking into account in the macro is important. Here is what I have so far:

Copy and paste the initial list function

Sub Report_P1()    
  Dim wsPivot As Worksheet: Set wsPivot = ThisWorkbook.Sheets("Pivot")
  Dim wsReport As Worksheet: Set wsReport = ThisWorkbook.Sheets("Report")

  wsPivot.Select
  Range("A4", Range("A65536").End(xlUp)).Select
  Application.CutCopyMode = False
  Selection.Copy
  wsReport.Select
  Range("A3").Select
  ActiveSheet.Paste          
End Sub

The filter tool I need help with

Sub Report_P2()
  Dim i As Integer
  Dim j As Integer
  Dim wsReport As Worksheet: Set wsReport = ThisWorkbook.Sheets("Report")
  Dim wsData As Worksheet: Set wsData = ThisWorkbook.Sheets("Data")

  For i = 1 To 10              
    If wsReport.Cells(i, 1) = wsData.Cells(i, 1) Then
      wsData.Select
      Range(i).Select
      'Application.CutCopyMode = False
      Range(i).Copy
      wsReport.Select
      Range(i).Select
      ActiveSheet.Paste
    End If
  Next i    
End Sub

Thank you for your help!

Is the range you are trying to copy is inside a `PivotTable` ? I'm asking since you have a worksheet named "Pivot" and I'm thinking you might be trying to look at the `PivotTable` in the `Field.Items` — Shai Rado, Apr 24 '17 at 14:27
Range(I)? Do you have ranges name 1 thru 10? The variable I is a row number and it is the same on both sheets. Is that correct? If so then you want to use rows(I), not range(I) if you want to copy and paste the entire row. For pasting you need to use range("A" & I).select to paste an entire row. Do you need to insert a new row before pasting? — John Muggins, Apr 24 '17 at 16:11

score 0 · Answer 1 · edited May 23 '17 at 11:46

Sub Report_P2()

Dim i As Integer
Dim j As Integer
Dim wsReport As Worksheet: Set wsReport = ThisWorkbook.Sheets("Report")
Dim wsData As Worksheet: Set wsData = ThisWorkbook.Sheets("Data")

  For i = 1 To 10

            If wsReport.Cells(i, 1) = wsData.Cells(i, 1) Then
            wsData.Select
                Rows(i).Select
                Selection.Copy
            wsReport.Activate
                Range("A" & i).Activate
                ActiveSheet.Paste
                End If
                Application.CutCopyMode = False
    Next i

End Sub

Or more concisely:

For i = 1 To 10
   If wsReport.Cells(i, 1) = wsData.Cells(i, 1) Then
      wsData.Rows(i).Copy Destination:=wsReport.Range("A" & i)
   End If
Next i

Copy and past with matching

1 Answers1