Trying to work around Runtime Error 1004: No Visible Cells

[Metzy7]

I'm trying to run a Macro that returns the revenue for a list of clients for a given office. I then set up an autofilter to hide rows with clients that have zero revenue, and then copy the visible clients (those with revenue) to an output worksheet. However, some offices don't have any clients with revenue, which is intentional. I'm looking for a work around so that it will capture the visible clients (when they exist) or just move on to the next office if there are not any clients with revenue for that office. Below is the macro that I have set up so far. Any help on how to put a work around into the macro would be greatly appreciated.

Sub Refresh()
Dim x As Integer
x = 3508
Do Until x = 3510
Worksheets("Client Revenue").Activate
Selection.AutoFilter Field:=1
Cells.Item(x, 2).Select
Selection.Copy
Range("F1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2
ActiveSheet.Calculate
Application.Run "ImportWorksheet"
ActiveSheet.Calculate

ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
Selection.AutoFilter Field:=1, Criteria1:="TRUE", Operator:=xlAnd
Selection.EntireColumn.Hidden = False
Dim R As Integer
For R = 6 To 96

Worksheets("Output").Activate
If Cells(R, 1) = Worksheets("Client Revenue").Cells(1, 6) Then
Sheets("Client Revenue").Select
Range("P2975:P3475").Select
Selection.SpecialCells (xlCellTypeVisible)
Selection.Copy
Sheets("Output").Select
Cells.Item(R, 5).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

End If

Next R
x = x + 1
Loop
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Worksheets("Client Revenue").Select
End Sub

 

[VoG]

Maybe like this

If Cells(R, 1) = Worksheets("Client Revenue").Cells(1, 6) Then
    On Error Resume Next
    Sheets("Client Revenue").Range("P2975:P3475").SpecialCells(xlCellTypeVisible).Copy
    Sheets("Output").Cells.Item(R, 5).PasteSpecial Paste:=xlPasteValues
    On Error GoTo 0
End If

 

[Metzy7]

Thank you very much. That's exactly what I needed.