AutoFilter Error message

[toant]

Hi, I am new to VBA and need help with this. I have an autofilter set to filter Sheet "Warehouse" column A with the set values in column A of Sheet "Filter" and copy rows that exist to Sheet "Inventory". The problem I come across is when values in Filter does not exist in "Warehouse" I get the error "No cells were found". How can I modify to get around this?

Sub With_AutoFilter()
Sheets("Inventory").Rows("2:" & Sheets("Inventory").Rows.Count).ClearContents
 Dim lr As Long, c As Range
 Application.ScreenUpdating = False
 lr = Sheets("Warehouse").Cells(Rows.Count, 1).End(xlUp).Row
 For Each c In Sheets("Filter").Range("A2", Sheets("Filter").Range("A" & Rows.Count).End(xlUp))
 With Sheets("Warehouse")
 .AutoFilterMode = False
 .Range("A1:A" & lr).AutoFilter field:=1, Criteria1:=c.Value
 .Range("A2:A" & lr).SpecialCells(12).EntireRow.Copy Sheets("Inventory").Cells(Rows.Count, "A").End(xlUp)(2)
 .AutoFilterMode = False
 End With
 Next c
 Application.ScreenUpdating = True
 
End Sub

 

[johnnyL]

What line is triggering the error?

 

[toant]

.Range("A2:A" & lr).SpecialCells(12).EntireRow.Copy Sheets("Inventory").Cells(Rows.Count, "A").End(xlUp)(2)

 

[toant]

What line is triggering the error?

.Range("A2:A" & lr).SpecialCells(12).EntireRow.Copy Sheets("Inventory").Cells(Rows.Count, "A").End(xlUp)(2)

 

[petertenthije]

Sub With_AutoFilter()
Sheets("Inventory").Rows("2:" & Sheets("Inventory").Rows.Count).ClearContents
 Dim lr As Long, c As Range
 Application.ScreenUpdating = False
 lr = Sheets("Warehouse").Cells(Rows.Count, 1).End(xlUp).Row
 For Each c In Sheets("Filter").Range("A2", Sheets("Filter").Range("A" & Rows.Count).End(xlUp))
 With Sheets("Warehouse")
 .AutoFilterMode = False
 .Range("A1:A" & lr).AutoFilter field:=1, Criteria1:=c.Value


' This has been added. 
' It counts in worksheet WAREHOUSE if the value you are looking for exists.
' If the value exists, it will copy over the data.
' If the value does not exist, it will abort the copy/paste, and proceed to the next lookup value.
NumberOfRowsWarehouse = WorksheetFunction.CountIf(Sheets("Warehouse").Range("A2:A" & lr), c.Value)
If NumberOfRowsWarehouse > 0 Then
 .Range("A2:A" & lr).SpecialCells(12).EntireRow.Copy Sheets("Inventory").Cells(Rows.Count, "A").End(xlUp)(2)
End If




 .AutoFilterMode = False
 End With
 Next c
 Application.ScreenUpdating = True
 
End Sub

 

[toant]

Sub With_AutoFilter()
Sheets("Inventory").Rows("2:" & Sheets("Inventory").Rows.Count).ClearContents
Dim lr As Long, c As Range
Application.ScreenUpdating = False
lr = Sheets("Warehouse").Cells(Rows.Count, 1).End(xlUp).Row
For Each c In Sheets("Filter").Range("A2", Sheets("Filter").Range("A" & Rows.Count).End(xlUp))
With Sheets("Warehouse")
.AutoFilterMode = False
.Range("A1:A" & lr).AutoFilter field:=1, Criteria1:=c.Value


' This has been added.
' It counts in worksheet WAREHOUSE if the value you are looking for exists.
' If the value exists, it will copy over the data.
' If the value does not exist, it will abort the copy/paste, and proceed to the next lookup value.
NumberOfRowsWarehouse = WorksheetFunction.CountIf(Sheets("Warehouse").Range("A2:A" & lr), c.Value)
If NumberOfRowsWarehouse > 0 Then
.Range("A2:A" & lr).SpecialCells(12).EntireRow.Copy Sheets("Inventory").Cells(Rows.Count, "A").End(xlUp)(2)
End If




.AutoFilterMode = False
End With
Next c
Application.ScreenUpdating = True

End Sub

Thank you. That worked.