I am trying to copy rows from a sheet to another sheet while filtering the rows by several criteria. I want to tell the program what column to look in on the first sheet and what value I want in that column. I use the following code to reference a module with a sub called "Copyrowsandpaste":
Copyrowsandpaste Worksheets("wobid"), Worksheets("res"), "S", "Completed"
where S is the column and Completed is the desired value. The module functions when I have only one criteria:
Sub Copyrowsandpaste(fromsheet As Worksheet, tosheet As Worksheet, Optional col1, Optional crit1)
Dim i As Long
Dim LastRow As Long
fromsheet.Activate
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Range(col1 & i) = crit1 _
Then
Range(col1 & i).EntireRow.Copy tosheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next i
End Sub
But when I try to add other criteria, I get an error:
Sub Copyrowsandpaste(fromsheet As Worksheet, tosheet As Worksheet, Optional col1, Optional crit1, _
Optional col2, Optional crit2, Optional col3, Optional crit3, Optional col4, Optional crit4, _
Optional col5, Optional crit5)
Dim i As Long
Dim LastRow As Long
fromsheet.Activate
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Range(col1 & i) = crit1 _
And Range(col2 & i) = crit2 _
And Range(col3 & i) = crit3 _
And Range(col4 & i) = crit4 _
And Range(col5 & i) = crit5 _
Then
Range(col1 & i).EntireRow.Copy tosheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next i
End Sub
I get a type mismatch error. Any ideas how can I fix this error?
Thank you!
Bookmarks