Using Paste After Auto Filter

[WireGuy8]

I am working on a project that needs to have filtering of an excel sheet then pasting in another sheet. Everything works great if there is data in the range. If there is no data I get a 1004 Error.

The range C3:C502 is filtered and all data is hidden, so I do not need the copy and paste, just move on. If there is data, then need the copy/paste.

' Filter Source Data
sheet4.Range("B1").AutoFilter Field:=2, Criteria1:="Direct Embed"

' Apply Filter Direct Embed
sheet4.Select
Range("C3:C502").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
sheet3.Select
Range("D595").PasteSpecial Paste:=xlPasteValues
sheet4.Select
Range("R3:R502").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
sheet3.Select
Range("I595").PasteSpecial Paste:=xlPasteValues
Range("Q3:Q502").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
sheet3.Select
Range("F595").PasteSpecial Paste:=xlPasteValues

 

[JoeMo]

You can add a condition to exit if the visible cell count exceeds 1 (assuming a header row).

If Selection.SpecialCells(xlCellTypeVisible).Count > 1 Then
    'rest  of  code
Else
    Exit sub
End If

 

[Fluff]

Do you have any data in col A, or is it completely blank?

 

[WireGuy8]

The only problem is there are hundreds of other lines going on before and after this code. For that reason, I cannot exit the sub

 

[WireGuy8]

Do you have any data in col A, or is it completely blank?

I have data in columns A-J

 

[WireGuy8]

This code works great when there is data in cells C3:C502, when those cells are hidden by the filter since there is no data, I keep getting the error.

 

[JoeMo]

The only problem is there are hundreds of other lines going on before and after this code. For that reason, I cannot exit the sub

You don't have to Exit - just use a GoTo statement to bypass the lines you don't want to execute.

 

[Fluff]

How about like

With Sheet4
   .Range("B1").AutoFilter Field:=2, Criteria1:="Direct Embed"
   .AutoFilter.Range.Offset(1).Columns("C").Copy
   Sheet3.Range("D595").PasteSpecial xlPasteValues
   .AutoFilter.Range.Offset(1).Columns("R").Copy
   Sheet3.Range("I595").PasteSpecial xlPasteValues
   .AutoFilter.Range.Offset(1).Columns("Q").Copy
   Sheet3.Range("F595").PasteSpecial xlPasteValues
End With

 

[WireGuy8]

How about like

With Sheet4
   .Range("B1").AutoFilter Field:=2, Criteria1:="Direct Embed"
   .AutoFilter.Range.Offset(1).Columns("C").Copy
   Sheet3.Range("D595").PasteSpecial xlPasteValues
   .AutoFilter.Range.Offset(1).Columns("R").Copy
   Sheet3.Range("I595").PasteSpecial xlPasteValues
   .AutoFilter.Range.Offset(1).Columns("Q").Copy
   Sheet3.Range("F595").PasteSpecial xlPasteValues
End With

That works perfect! Thank you!

 

[Fluff]

Glad we could help & thnaks for the feedback.