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:
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