Creating Varied Pivot Tables based on a Range Name

[TheRedCardinal]

I have developed a Macro for my work, and it's going brilliantly. Thanks to everybody on here so far who has helped.

The next stage is for me to create pivot tables of the data.

I have a table called "Final" data which contains the data for the pivot table, however the format of the pivot table (specifically the row labels) varies depending on the contents of 2 Ranges, RepCountry and RepDirection. There are around 20 combinations that could be used.

I have my table working correctly but now need to introduce this varied set up of it.

My first thought was simple IF statements, but this would create 20 if statements which seems a lot.

I was wondering if there was a better way to do it, and I'll spend my morning working on some ideas.

The main one I have is to create as a one off a series of ranges in my data sheet that contain the names of the row fields for each combination.

I then don't know how to loop through these and create the fields, especially when the numbers will vary.

I was thinking something like:

For Each CellA in CombinedRange
With PivotTable("FinalPivot")

<add pivotfield="" as="" per="" the="" value="" of="" cella="" which="" names="" field="" in="" table="" to="" use="">"Add Row Field using the value of CellA to refer to table headers"
.Orientation = xlRowField
.Position = Counter

Counter = Counter +1
Next CellA
End With

My question is will this work,and if so how do I code the <add pivotfield=""> bit "Add Row Field" - my current code just adds row fields using:

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount")

And I don't see a way to modify that to pull in the value of the field.

Thanks!</add></add>

 

[TheRedCardinal]

So I have given it a go, and came up with this code:

For Each CellA In WS2.Range("G17:I17")
    
        With ActiveSheet.PivotTables("FinalPivot").PivotFields(CellA.Value)
        
        Counter = Counter + 1
        .Orientation = xlDataField
        .Position = Counter
        
        End With

    Next CellA

I have hard coded the Range for now.
In that range are 3 cells, each containing a text string that matches a heading of the table which is the data source.

What I wanted it to do was create three row fields, position set by the counter variable.

Instead it produces the table, but with no row fields, just the grand total.

Any ideas what I have done wrong?

 

[TheRedCardinal]

Well that was embarrassing

Clue 1 - make sure you define the worksheets properly, WS2 was not the correct sheet.
Secondly - make sure you use the right code!

Orientation is of course .xlRowField not .xlDataField

Sigh

So it's kind of working now as follows:

For Each CellA In WS3.Range("G17:I17")
    
        With ActiveSheet.PivotTables("FinalPivot").PivotFields(CellA.Value)
        
        Counter = Counter + 1
        .Orientation = xlRowField
        .Position = Counter
        
        End With


    Next CellA

I'll post this here now and open a new thread with my more general question on setting up the range references.

Thanks for anybody who looked!