vba On Error GoTo

[daves120]

Hi folks,

This macro works when testing, but does not work when running as part of a sequence. It is supposed to exit the sub if a site (country) does not exist, and there is one known site name that does not exist in the pivot table. If a site does exist, then it is supposed to copy the filtered data from a pivot table and paste it to a different tab in the same workbook.

It is not copying and pasting the data from the pivot table when running the macro to produce a report for every site in the list. Any guidance would be greatly appreciated.

[Sub FilterFTEPivot()

Dim pt As PivotTable
Dim pf As PivotField
Set pt = wsFTEPT.PivotTables("PivotTable3")


On Error GoTo ErrorHandler


wsFTEPT.Select
With Selection
pt.PivotFields("Position Country").ClearAllFilters
pt.PivotFields("Position Country").CurrentPage = Range("Site").Value
End With

ActiveWorkbook.RefreshAll

wsFTEs.Select
Range("A4").Select
Range(Selection, Selection.End(xlDown)).ClearContents
wsFTEPT.Select
Range("A4").Select
Range(Selection, Selection.End(xlDown)).Copy
wsFTEs.Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
'End If

With wsControl
.Activate
.Range("A1").Activate
End With


ErrorHandler:
Exit Sub


End Sub]

 

[daves120]

Found the cause of one of the issues. A macro was deselected in the list when generating a report for all sites in the list. The issue now is that when a site does not exist, the entire unfiltered pivot table list is copied and pasted into another tab on the workbook. What needs to happen is that is goes directly to "Exit Sub" when the site does not exist. Once again, any guidance would be greatly appreciated.