VBA: Copy and Paste to Different Tab

[unknownymous]

Hello Gurus,

Hope all is well.

I have two tabs namely Excel 1 and Excel 2. Excel 1 contains the data and I need to filter it to Copy+Paste in the Excel 2 tab based on the following criteria.

Copy and paste whole row if contains these:
1. Column B = Look for "Math"
2. Column C = Look for "UK"
3. Column E = Look for "Comments"

Note that some column have blank cells so filtering I think is the fastest way.


Thank you!

 

[Legacy 143009]

Sub myFunction()
  Dim lRow As Long
  lRow = Worksheets("Excel 1").Cells(Rows.Count, 2).End(xlUp).Row
  With Worksheets("Excel 1")
    For i = 1 to lRow
      If InStr(.Cells(i, 2).Value, "Math") * InStr(.Cells(i, 3).Value, "UK") * InStr(.Cells(i, 5).Value, "Comments") > 0 Then
        .Rows(i).Copy
        Worksheets("Excel 1").Cells(Rows.Count, 2).End(xlUp).Row.Rows(Worksheets("Excel 2").Cells(Rows.Count, 2).End(xlUp).Row + 1).EntireRow.Insert
      End If
    Next
  End With
End Sub

 

[unknownymous]

Sub myFunction()
  Dim lRow As Long
  lRow = Worksheets("Excel 1").Cells(Rows.Count, 2).End(xlUp).Row
  With Worksheets("Excel 1")
    For i = 1 to lRow
      If InStr(.Cells(i, 2).Value, "Math") * InStr(.Cells(i, 3).Value, "UK") * InStr(.Cells(i, 5).Value, "Comments") > 0 Then
        .Rows(i).Copy
        Worksheets("Excel 1").Cells(Rows.Count, 2).End(xlUp).Row.Rows(Worksheets("Excel 2").Cells(Rows.Count, 2).End(xlUp).Row + 1).EntireRow.Insert
      End If
    Next
  End With
End Sub

I tried the code but I'm getting a "Compile Error: Variable note defined" on the "i".

 

[Legacy 143009]

Most probably you are using option explicit. Ok, try like this:

Sub myFunction()
  Dim lRow As Long, i As Long
 
  With Worksheets("Excel 1")
    lRow = .Cells(Rows.Count, 2).End(xlUp).Row
    For i = 1 to lRow
      If InStr(.Cells(i, 2).Value, "Math") * InStr(.Cells(i, 3).Value, "UK") * InStr(.Cells(i, 5).Value, "Comments") > 0 Then
        .Rows(i).Copy
        Worksheets("Excel 2").Rows(Worksheets("Excel 2").Cells(Rows.Count, 2).End(xlUp).Row + 1).EntireRow.Insert
      End If
    Next
  End With
End Sub

 

[unknownymous]

Thank you

Most probably you are using option explicit. Ok, try like this:

Sub myFunction()
  Dim lRow As Long, i As Long
 
  With Worksheets("Excel 1")
    lRow = .Cells(Rows.Count, 2).End(xlUp).Row
    For i = 1 to lRow
      If InStr(.Cells(i, 2).Value, "Math") * InStr(.Cells(i, 3).Value, "UK") * InStr(.Cells(i, 5).Value, "Comments") > 0 Then
        .Rows(i).Copy
        Worksheets("Excel 2").Rows(Worksheets("Excel 2").Cells(Rows.Count, 2).End(xlUp).Row + 1).EntireRow.Insert
      End If
    Next
  End With
End Sub