PivotTable autofilter from defined name range

[Spikenaylor]

Hi

My code on worksheet activate is

Private Sub Worksheet_activate()    Dim pt As PivotTable
    Dim RolePick As Range
    Set RolePick = [Batch_ChartNumbers]
       For Each pt In Sheets("Debox Flock report").PivotTables
        Filter_PivotField pvtField:=pt.PivotFields("Batch Report"), varItemList:=Application.Transpose(RolePick)
    Next pt


End Sub

The defined name range "Batch_ChartNumbers" is populated by the end user and a report is generated, once the report is generated, the spreadsheet closes deleting the end users inputs, next time the spreadsheet is open the end users enters new data.

Works great until the defined name range doesn't contain anything and the report button is pressed or the end user clicks on the pivottable worksheet.

tried a few methods to fix, but can't get it to filter to blanks or nothing until the next time the defined name range is poulated, it keeps the previous user entered data in the pivot table "Batch Report" field.

Could anyone help please
Thanks very much

 

[Spikenaylor]

Found different code to do the same job, got it to work with just one value in the range, but if the range is empty, the object does not exist and the code bombs out. I just need to work out, if the defined name range is actually empty (therefore does not exist), I need the pivot table to filter down to blanks or nothing. at the moment it retains previous data.
If the defined name range contains one or more values, then everything works correctly.

Private Sub Worksheet_activate()
 
    Dim pt As PivotTable, test As Boolean
   
    Set pt = ActiveSheet.PivotTables("PivotTable1")
       
    If [Batch_ChartNumbers].Cells.Count = 1 Then
        Filter_PivotField pvtField:=pt.PivotFields("Batch Report"), varItemList:=Array([Batch_ChartNumbers].Value)
    Else
        Filter_PivotField pvtField:=pt.PivotFields("Batch Report"), varItemList:=Application.Transpose([Batch_ChartNumbers])
    End If
 
 
End Sub

Function Filter_PivotField(pvtField As PivotField, varItemList As Variant)
    Dim strItem1 As String
    Dim i As Long
    On Error GoTo Errorhandler:
    Application.ScreenUpdating = False
 
    strItem1 = varItemList(LBound(varItemList))
        With pvtField
        .PivotItems(strItem1).Visible = True
        For i = 1 To .PivotItems.Count
            If .PivotItems(i) <> strItem1 And _
                  .PivotItems(i).Visible = True Then
                .PivotItems(i).Visible = False
            End If
        Next i
        For i = LBound(varItemList) + 1 To UBound(varItemList)
            .PivotItems(varItemList(i)).Visible = True
        Next i
    End With
    Exit Function
Errorhandler:
    MsgBox "Error while trying to process item: " & varItemList(i)
End Function

Error Message is

Run-Time Error '424':
Object Required