# VBA: Copy and Paste to Different Tab



## unknownymous (Dec 20, 2022)

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!


----------



## Flashbond (Dec 20, 2022)

```
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 (Dec 20, 2022)

Flashbond said:


> ```
> Sub myFunction()
> Dim lRow As Long
> lRow = Worksheets("Excel 1").Cells(Rows.Count, 2).End(xlUp).Row
> ...


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


----------



## Flashbond (Dec 20, 2022)

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 (Dec 21, 2022)

Thank you 


Flashbond said:


> Most probably you are using option explicit. Ok, try like this:
> 
> ```
> Sub myFunction()
> ...


----------

