Hi Juan!

It seems to me that you've already done all the hard stuff :) now it's
just a matter of substituting a couple lines of code. In VBA For...To
Next works like this:

Dim I As Long
For K=1 To 100
For J=1 To 100
For I=1 To 100
'do some stuff
Next I
Next J
Next K

So if you need to build all possible Pivot Tables based on 8
countries, 2 product families and 4 offering family, I guess you could
write something like this:



Countries=Array("USA","Canada","Brazil",...,)
Products=Array("ProductA","ProductB")
Offerings=Array("Offer1",Offer2",..)

For K=1 to UBound(Countries)
ctryParam=Countries(K)
For J=1 to UBound(Products)
prdFmly = Products(J)
For I=1 to UBound(Offerings)
offering = Offerings(I)
' here you can put the rest of your code, from the
line
' 'Check that the Pivot doesn't exist
'going on
Next I
Next J
Next K

Be warned, though, you'll end up having 8x2x4=64 worksheets in your
workbook! Let me know if this helped,

Best Regards,

Sergio Rossi

On 5 Mar, 17:10, Juan Correa
wrote:
Hello,

I have been working on a set of tools for my boss for the past few weeks and
with the help of some of the gurus here I have been able to get them to work
so far. *But as you know; the more information you give someone, the more
information they will want and now my boss has asked me to implement
something else for the tool.
Here is what I have so far:
I have a sub() that creates a Pivot Table based on criteria selected by the
user on three cells with lists like this:

Cell 1 - Country List -- 8 Countries
Cell 2 - Product Family -- 2 Options
Cell 3 - Offering Family -- 4 Options

The macro works fine at creating individual pivots based on the selected
criteria. *But now my boss has decided that he's lazy and doesn't want to set
up the criteria for all the possible combinations, so he asked me to make a
button that will create ALL the possible Pivot Tables at once.

Here is the code for the macro as it is right now:

Sub CtryPivot()
* * Application.ScreenUpdating = False
* * ActiveWindow.DisplayGridlines = False

' Declarations
* * Dim OptWks As Worksheet
* * Dim PTCache As PivotCache
* * Dim ctryParam As String
* * Dim prdFmly As String
* * Dim offering As String
* * Dim DataWks As Worksheet

' Make sure we're looking in the right place
* * Set OptWks = Worksheets("Options")
* * ctryParam = OptWks.Range("E7").Value
* * prdFmly = OptWks.Range("E10").Value
* * offering = OptWks.Range("E13").Value

' Check that the Parameters Have been entered
* * On Error Resume Next
* * If OptWks.Range("E7").Value = "" Or OptWks.Range("E10").Value = "" Or
OptWks.Range("E13").Value = "" Then
* * * * MsgBox "Select ALL Paramters" & vbNewLine & "Before Creating" &
vbNewLine _
* * * * & "Your Pivot Table", vbCritical, "Warning!"
* * * * OptWks.Range("E7").Value = ""
* * * * OptWks.Range("E10").Value = ""
* * * * OptWks.Range("E13").Value = ""
* * * * On Error GoTo 0
* * Else

' Check that the Pivot doesn't exist
* * Dim wSheet As Worksheet
* * On Error Resume Next
* * Set wSheet = Sheets(ctryParam & "-" & prdFmly & "-" & offering)
* * If wSheet Is Nothing Then

* * * * ' Make sure the Data tab was formatted before generating the Pivot
* * * * * * On Error Resume Next
* * * * * * Set DataWks = Worksheets("Data")
* * * * * * If WorksheetFunction.CountA(DataWks.Cells) = 0 Then
* * * * * * * * MsgBox "The Data Tab Is Empty" & vbNewLine & "Run the Format
Data" _
* * * * * * * * & vbNewLine & "Before Creating the Pivot Table", vbCritical,
"Warning!"
* * * * * * Else

* * * * ' Create the Country Pivot Base On Selected Parameters
* * * * * * Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name =
ctryParam & "-" & prdFmly _
* * * * * * & "-" & offering

* * * * * * Set PTCache =
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:="PivotData")
* * * * * * PTCache.CreatePivotTable TableDestination:=Range("A3"),
TableName:=ctryParam & "Pivot"
* * * * * * ActiveWindow.DisplayGridlines = False

* * * * ' Set a Pivot Table variable to our new Pivot Table
* * * * * * Set Pt = ActiveSheet.PivotTables(ctryParam & "Pivot")

* * * * ' The layout of the Pivot Table
* * * * * * Pt.AddFields RowFields:=Array( _
* * * * * * * * "Forecast Category", "Account Name"), ColumnFields:="Booked
Month", _
* * * * * * * * PageFields:=Array("Country", "Product Family", "Product
Category")

* * * * * * With Pt.PivotFields("Total Price (converted)")
* * * * * * * * .Orientation = xlDataField
* * * * * * * * .Function = xlSum
* * * * * * * * .NumberFormat = "#,##0"
* * * * * * End With

* * * * * * With Cells.Font
* * * * * * * * .Size = 8
* * * * * * End With

* * * * * * With Pt.PivotFields("Product Family")
* * * * * * * * .CurrentPage = prdFmly
* * * * * * End With

* * * * * * With Pt.PivotFields("Country")
* * * * * * * * .Orientation = xlPageField
* * * * * * * * .Position = 3
* * * * * * * * .CurrentPage = ctryParam
* * * * * * End With

* * * * * * With Pt.PivotFields("Product Category")
* * * * * * * * .Orientation = xlPageField
* * * * * * * * .Position = 1
* * * * * * * * .CurrentPage = offering
* * * * * * End With

* * * * ' Tiddy up a bit!
* * * * * * Pt.PivotFields("Account Name").AutoSort xlDescending, "Sum of
Total Price (converted)"
* * * * * * Pt.PivotFields("Forecast
Category").PivotItems("Commit").Position = 1
* * * * * * Pt.PivotFields("Forecast
Category").PivotItems("Upside").Position = 2
* * * * * * Pt.PivotFields("Forecast
Category").PivotItems("Pipeline").Position = 3
* * * * * * Pt.PivotFields("Forecast
Category").PivotItems("Closed").Position = 4
* * * * * * Pt.PivotFields("Forecast
Category").PivotItems("Pipeline").ShowDetail = False
* * * * * * Pt.PivotFields("Forecast
Category").PivotItems("Closed").ShowDetail = False
* * * * * * Cells.EntireColumn.AutoFit
* * * * * * OptWks.Range("E7").Value = ""
* * * * * * OptWks.Range("E10").Value = ""
* * * * * * OptWks.Range("E13").Value = ""
* * * * * * ActiveWorkbook.ShowPivotTableFieldList = False
* * * * * * Application.ScreenUpdating = True
* * * * Set wSheet = Nothing
* * * * On Error GoTo 0
* * * * End If
* * Else
* * * * MsgBox "The Pivot Table for:" & vbNewLine & ctryParam & "-" &
prdFmly _
* * * * & "-" & offering & vbNewLine & "Allready Exists", vbCritical,
"Warning!"
* * * * Set wSheet = Nothing
* * * * OptWks.Range("E7").Value = ""
* * * * OptWks.Range("E10").Value = ""
* * * * OptWks.Range("E13").Value = ""
* * * * On Error GoTo 0
* * End If
* * End If
* * Set DataWks = Nothing
End Sub

The code creates a Pivot Table based on the parameters entered into the
predetermined cells in my Options worksheet and then creates a new worksheet
named using those paramters as well. *I have checkpoints in there to make
sure that no duplicate worksheets are attempted, and also to make sure that
the parameters have been set before running the macro.

My question is:
How would I go about setting up a modified version of the code so that it
generates all possible combinations of Pivot Tables based on the three
variables (ctryParam, prdFmly, offering)?
My guess is that I'll need to do some sort of For_Next loop, but this would
be the first time I've done loops in VBA and I have no idea how to go about
this.

Thanks
Juan Correa