VBA select pivot table by Activecell

Question

In my excel sheet, the pivot table is copied from another file every month, therefore the name will change every time when a new pivot table is copied to my worksheet. I want to use macro to set the pivot table filter but don't know how to refer the table without knowing the name. I use the code below, it sometimes work, but sometimes would come with error. Could you please take a look if there is any error in my code? or if there is another solution for my situation?

Another problem is, my code adjust two pivot tables in two worksheet, however, the code sometimes (most of the time) end up working on only one sheet. Does any body know the reason?

Sub getdeb()
Dim irfile As String
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet

With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Please select a file"
    .ButtonName = "Select"
    .InitialFileName = "C:\"
    If .Show = -1 Then
        irfile = .SelectedItems(1)

        Set wbtarget = ThisWorkbook
        Set wbsource = Workbooks.Open(irfile)

        Set ws1 = wbsource.Sheets("NN Re inzake ART Non-Life")

        Set ws2 = wbsource.Sheets("NN Re inzake ART Life")

        ws2.Cells.Copy Destination:=wbtarget.Sheets("Life").Range("A1")

        ws1.Cells.Copy Destination:=wbtarget.Sheets("NonLife").Range("A1")

        wbsource.Close

        Else
            MsgBox "No file was selected"

    End If
End With

With ThisWorkbook

Set ws3 = Sheets("Life")
Set ws4 = Sheets("NonLife")
ws3.Cells.Clear
ws4.Cells.Clear

End With

With ws3

    With ActiveCell.PivotTable.PivotFields("GAUDI rubriek")
    .PivotItems("A_INT_RENTS_ACCR    ").Visible = False
    .PivotItems("A_OTH_ACCR_ASSETS   ").Visible = False
    .PivotItems("L_COST_PAYABLE      ").Visible = False
    .PivotItems("L_CRED_DIR          ").Visible = False
    .PivotItems("L_CRED_OTH_3P       ").Visible = False
    .PivotItems("L_CRED_OTH_IC       ").Visible = False
    .PivotItems("L_CRED_REINS_IC     ").Visible = False
    .PivotItems("L_DEF_TAX_LIAB      ").Visible = False
    .PivotItems("L_INCOME_TAX        ").Visible = False
    .PivotItems("L_INT_RENTS_ACCR    ").Visible = False
    .PivotItems("L_OTH_PROV          ").Visible = False
    .PivotItems("A_TAX_REC           ").Visible = False
    .PivotItems("L_CRED_REINS_3P     ").Visible = False
    End With

    With ActiveCell.PivotTable.PivotFields("Issuer")
    .ClearAllFilters
    .EnableMultiplePageItems = True
    .PivotItems(" ").Visible = False
    End With

    .Range("N9") = "GRID Mapping DvS"
    .Range("O9") = "GRID Name Mapping DvS"
    .Range("P9") = "Country Mapping DvS"
    .Range("Q9") = "Instrument ID DvS added"
    .Range("R9") = "Country Mapping DvS2"
    .Range("R9") = "Thomson-Reuters id"
    .Range("Q10") = "=$B10&"" ""&$A10"
    .Range("N10:R100").FillDown

End With

With ws4

    With ActiveCell.PivotTable.PivotFields("GAUDI rubriek")
    .PivotItems("A_INT_RENTS_ACCR    ").Visible = False
    .PivotItems("A_OTH_ACCR_ASSETS   ").Visible = False
    .PivotItems("L_COST_PAYABLE      ").Visible = False
    .PivotItems("L_CRED_DIR          ").Visible = False
    .PivotItems("L_CRED_OTH_3P       ").Visible = False
    .PivotItems("L_CRED_OTH_IC       ").Visible = False
    .PivotItems("L_CRED_REINS_IC     ").Visible = False
    .PivotItems("L_DEF_TAX_LIAB      ").Visible = False
    .PivotItems("L_INCOME_TAX        ").Visible = False
    .PivotItems("L_INT_RENTS_ACCR    ").Visible = False
    .PivotItems("L_OTH_PROV          ").Visible = False
    .PivotItems("A_TAX_REC           ").Visible = False
    .PivotItems("L_CRED_REINS_3P     ").Visible = False
    End With

    With ActiveCell.PivotTable.PivotFields("Issuer")
    .ClearAllFilters
    .EnableMultiplePageItems = True
    .PivotItems(" ").Visible = False
    End With

    .Range("N9") = "GRID Mapping DvS"
    .Range("O9") = "GRID Name Mapping DvS"
    .Range("P9") = "Country Mapping DvS"
    .Range("Q9") = "Instrument ID DvS added"
    .Range("R9") = "Country Mapping DvS2"
    .Range("R9") = "Thomson-Reuters id"
    .Range("N10:R500").FillDown
    .Calculate

End With
End Sub

Vityata Vityata · Accepted Answer · 2018-09-27T07:57:34

In order to refer to a pivot table and pivot field, it should exist. This part of the code deletes all pivot tables on ws3, thus it cannot be referred further:

ws3.Cells.Clear
With ws3
    With ActiveCell.PivotTable.PivotFields("GAUDI rubriek")

Try to amend your code to this:

With ws3.Range(ActiveCell.Address).PivotTable.PivotFields("GAUDI rubriek")

everywhere for both ws3 and ws4. Thus you would be referring the relevant ActiveCell address on the relevant worksheet.

VBA select pivot table by Activecell

1 Answers