Handling error in Pivot source update

Question

I have create macro to change the source of Pivot in all worksheets. In my workbook there has sheets one with CityName, like 'Mumbai'(Which is pivot sheet) and source data sheet with Name 'MumbaiData'(which is source sheet), It's popping an error bcz sometime i don't have source sheet. How could i handle this Error to resume to next sheet's Pivot

My code

Sub pivotsourcechange()


Dim ws As Worksheet
Dim pt As PivotTable

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
ws.PivotTableWizard SourceType:=xlDatabase, SourceData:=Worksheets(ActiveSheet.Name & " PV").UsedRange
Next pt
Next ws
End Sub

To be clear, in the event of the sheet not existing, you do not want the pivot table to be changed at all? — Steph Locke

Jake Bathman Jake Bathman · Accepted Answer · 2013-06-19T21:11:46

It's generally not a good idea to use often, but On Error Resume Next would work fine here:

Sub pivotsourcechange()

  Dim ws As Worksheet
  Dim pt As PivotTable

  On Error Resume Next
  For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
      ws.PivotTableWizard SourceType:=xlDatabase, SourceData:=Worksheets(ActiveSheet.Name & " PV").UsedRange
    Next pt
  Next ws
  On Error Goto 0
End Sub

There's lots more that can be done with On Error, including going to another spot of code to handle the error and returning. Check out this link for info: http://support.microsoft.com/kb/141571

Handling error in Pivot source update

1 Answers