Ignore error on pivot table filtering

Siggi

New Member
Joined
Apr 27, 2018
Messages
5
Dear all,

I have two pivot tables on one worksheet, one for the input and one for the output of material. Both are based on different tables and have two filters for year and period of the year. On another sheet I can select a year and a period, hit a button and the filter of both pivot tables are updated. So far, it works quite well. However, if I don't there is for example no output data available for a specific period it obviously gives me an error that it cannot be filtered like that (Run-time error '1004': Application-defined or object-defined error).
Is there a work-around to ignore the error and fill in the filter anyway, even if it returns no data for the columns or rows?

Many thanks for your support.
S

Here is the code of the macro:

Code:
Sub Report_Filter()
'Set the Variables to be used
Dim pt1 As PivotTable
Dim pt2 As PivotTable
Dim FieldYear1 As PivotField
Dim FieldPeriod1 As PivotField
Dim FieldYear2 As PivotField
Dim FieldPeriod2 As PivotField
Dim NewYear As String
Dim NewPeriod As String

'Amend here to filter your data
Set pt1 = Worksheets("Report").PivotTables("DOIC Report Input")
Set pt2 = Worksheets("Report").PivotTables("DOIC Report Output")
Set FieldYear1 = pt1.PivotFields("Reporting period year")
Set FieldPeriod1 = pt1.PivotFields("Reporting period")
Set FieldYear2 = pt2.PivotFields("Reporting period year")
Set FieldPeriod2 = pt2.PivotFields("Reporting period")
NewYear = Worksheets("Start").Range("C17").Value
NewPeriod = Worksheets("Start").Range("C18").Value

'This updates and refreshes the PIVOT table
With pt1
FieldYear1.ClearAllFilters
FieldPeriod1.ClearAllFilters
FieldYear1.CurrentPage = NewYear
FieldPeriod1.CurrentPage = NewPeriod
pt1.RefreshTable
End With

With pt2
FieldYear2.ClearAllFilters
FieldPeriod2.ClearAllFilters
FieldYear2.CurrentPage = NewYear
FieldPeriod2.CurrentPage = NewPeriod
pt2.RefreshTable
End With

Sheets("Report").Select

End Sub
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top