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:=xlDatabase,
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

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 <JuanCor...@discussions.microsoft.com>
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 makea
> 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:=xlDatabase,
> 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

Thank you very much Sergio.

I will test this out and will let you know if it worked.

Cheers
Juan Correa

"deltaquattro" wrote:

> 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 <JuanCor...@discussions.microsoft.com>
> 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:=xlDatabase,
> > 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
>
> .
>

Ciao Juan!

Sure, you don't need to hard code the variables, and as a matter of
fact rule you should never do that because it leads to obscure and
difficult to mantain code. I just wrote my code snippet that way as an
short example, but it was not meant to be used as actual code. Since
your data come from a worksheet, say "Source Data": I assume that
Countries, Products and Offerings are stored in three different
columns. Then you can just assign the values for Countries, Products
and Offerings to three Range objects, and refer to them in your code.
The only "tricky" part may be that you you don't know how many non-
empty cells there are in each column. This is easily overcome by using
the CurrentRegion property, if data are not in adjacent columns,
otherwise you can use the CountA worksheet function to get the number
of non-empty cells. I assume the first case, so let's suppose you have
Countries in column A, Products in column C and Offerings in column E:

A B C D E
Argentina ProdA Family1
Brazil ProdB Family2
Chile & Peru Family3
Colombia Family4
Distributors
Mexico
PR & DR

Then just run the following:

Sub driver()
Dim SheetName As String
SheetName="Source Data"
Call foobar(SheetName)
End Sub

Sub foobar(SheetName As String)

Dim Countries As Range, Products As Range, Offerings As Range
Dim R1 As Range, R2 As Range, R3 As Range
Dim ctryParam As String
Dim prdFmly As String
Dim offering As String

Set Countries = Worksheets(SheetName).Range("A1").CurrentRegion
Set Products = Worksheets(SheetName).Range("C1").CurrentRegion
Set Offerings = Worksheets(SheetName).Range("E1").CurrentRegion

Dim I As Long
I = 0
For Each R1 In Countries
ctryParam = R1.Value
For Each R2 In Products
prdFmly = R2.Value
For Each R3 In Offerings
offering = R3.Value
I = I + 1
Cells(I, 7) = ctryParam & prdFmly & offering
Next
Next
Next
End Sub

and column G will be populated with all possible combinations of
Countries, Products and Offerings. Now just substitute the trivial
part

I = I + 1
Cells(I, 7) = ctryParam & prdFmly & offering

with your actual code and off you go.

Best Regards,

Sergio Rossi (deltaquattro)



On 8 Mar, 22:01, Juan Correa <JuanCor...@discussions.microsoft.com>
wrote:
> Sergio...
>
> I've run into another bump on the road so to speak...
>
> I know I can hard code my array variables and then use them in the loop.
> But since I have the tree lists stored in ranges on a worksheet within my
> workbook. I was wondering how I would go about storing the values on those
> ranges to their respective array variables programatically?
>
> IE
> Country range (D28)
> Argentina
> Brazil
> Chile & Peru
> Colombia
> Distributors
> Mexico
> PR & DR
>
> How would I store those values to my countries variable without hard-coding
> them?
>
> Thanks
> Juan Correa
>
> "deltaquattro" wrote:
> > 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 <JuanCor...@discussions.microsoft.com>
> > 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:=xlDatabase,
> > > 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
>
> > .

Thank you very much Sr.
That did it.
I'm off to new pastures.

Cheers
JC

"deltaquattro" wrote:

> Ciao Juan!
>
> Sure, you don't need to hard code the variables, and as a matter of
> fact rule you should never do that because it leads to obscure and
> difficult to mantain code. I just wrote my code snippet that way as an
> short example, but it was not meant to be used as actual code. Since
> your data come from a worksheet, say "Source Data": I assume that
> Countries, Products and Offerings are stored in three different
> columns. Then you can just assign the values for Countries, Products
> and Offerings to three Range objects, and refer to them in your code.
> The only "tricky" part may be that you you don't know how many non-
> empty cells there are in each column. This is easily overcome by using
> the CurrentRegion property, if data are not in adjacent columns,
> otherwise you can use the CountA worksheet function to get the number
> of non-empty cells. I assume the first case, so let's suppose you have
> Countries in column A, Products in column C and Offerings in column E:
>
> A B C D E
> Argentina ProdA Family1
> Brazil ProdB Family2
> Chile & Peru Family3
> Colombia Family4
> Distributors
> Mexico
> PR & DR
>
> Then just run the following:
>
> Sub driver()
> Dim SheetName As String
> SheetName="Source Data"
> Call foobar(SheetName)
> End Sub
>
> Sub foobar(SheetName As String)
>
> Dim Countries As Range, Products As Range, Offerings As Range
> Dim R1 As Range, R2 As Range, R3 As Range
> Dim ctryParam As String
> Dim prdFmly As String
> Dim offering As String
>
> Set Countries = Worksheets(SheetName).Range("A1").CurrentRegion
> Set Products = Worksheets(SheetName).Range("C1").CurrentRegion
> Set Offerings = Worksheets(SheetName).Range("E1").CurrentRegion
>
> Dim I As Long
> I = 0
> For Each R1 In Countries
> ctryParam = R1.Value
> For Each R2 In Products
> prdFmly = R2.Value
> For Each R3 In Offerings
> offering = R3.Value
> I = I + 1
> Cells(I, 7) = ctryParam & prdFmly & offering
> Next
> Next
> Next
> End Sub
>
> and column G will be populated with all possible combinations of
> Countries, Products and Offerings. Now just substitute the trivial
> part
>
> I = I + 1
> Cells(I, 7) = ctryParam & prdFmly & offering
>
> with your actual code and off you go.
>
> Best Regards,
>
> Sergio Rossi (deltaquattro)
>
>
>
> On 8 Mar, 22:01, Juan Correa <JuanCor...@discussions.microsoft.com>
> wrote:
> > Sergio...
> >
> > I've run into another bump on the road so to speak...
> >
> > I know I can hard code my array variables and then use them in the loop.
> > But since I have the tree lists stored in ranges on a worksheet within my
> > workbook. I was wondering how I would go about storing the values on those
> > ranges to their respective array variables programatically?
> >
> > IE
> > Country range (D28)
> > Argentina
> > Brazil
> > Chile & Peru
> > Colombia
> > Distributors
> > Mexico
> > PR & DR
> >
> > How would I store those values to my countries variable without hard-coding
> > them?
> >
> > Thanks
> > Juan Correa
> >
> > "deltaquattro" wrote:
> > > 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 <JuanCor...@discussions.microsoft.com>
> > > 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:=xlDatabase,
> > > > 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 = ""