'Excel VBA With Statements for Pivot Tables

I have a subroutine I'm writing to make a pivot table, but I'm having some trouble with syntax when formatting some parts of the table.

When trying to format all row fields I'd like to be able to use a with statement as I would when formatting outside of a pivot table.

While trying to learn the syntax, the macro recorder however gave me the following which I'm not sure how to adapt...

    ActiveSheet.PivotTables("PivotTableMain").PivotSelect "'Lot Size'[All;Total]", _
        xlDataAndLabel, True

    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

I'd obviously like to avoid using PivotSelect/Selection, but the main issue is this macro is going to be triggered from another sheet, and even switching ActiveSheet to the target sheet name results in the original sheet being modified.

WsPivot.PivotTables("PivotTableMain").PivotSelect...

This doesn't work either.

I'm looking for something like the following,

With WsPivot.PivotTables("PivotTableMain").("'Lot Size'[All;Total]").interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

If anyone can offer any help it'd be appreciated.

Thanks,

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'Excel VBA With Statements for Pivot Tables

Sources

Related Questions