CODEWITHSUNDEEP
excelpivot-tablevba
MJV
MJV

Reputation: 350

Reference the only pivot table on active worksheet

I am calling a procedure from a number of worksheets that sets the print area to a pivot table's address. 

With ActiveSheet

    .PageSetup.PrintArea = .PivotTables("Pivot_Table_Name_1").TableRange1.Address

End With

ActiveWindow.SelectedSheets.PrintPreview

There will only ever be one pivot table on the active worksheet. I am wondering if there is a way to reference 'the only pivot table on the worksheet' to avoid referencing the name of every pivot table individually (there are many).

Thank you.

Upvotes: 0

Views: 669

Answers (1)

user6432984
user6432984

Reputation:

You can refer to the PivotTable by it's index in the Worksheet's PivotTable collection.

Here are two ways to iterate over the worksheets>

Dim ws As Worksheet

Using an array yo limit which worksheets to iterate over

For Each ws In Worksheets(Array("Sheet1", "Sheet2"))
    With ws
        .PageSetup.PrintArea = .PivotTables(1).TableRange1.Address
    End With
Next

Test to see if there are any PivotTables on the worksheet

For Each ws In Worksheets
    With ws
        If .PivotTables.Count > 0 Then
            .PageSetup.PrintArea = .PivotTables(1).TableRange1.Address
        End If
    End With
Next

Upvotes: 1

Related Questions