|
Create multiple Pivots based on list of parameters
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 |
Create multiple Pivots based on list of parameters
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 |
Create multiple Pivots based on list of parameters
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 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 . |
Create multiple Pivots based on list of parameters
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 (D2:D8) 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 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 . |
Create multiple Pivots based on list of parameters
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 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 (D2:D8) 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 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 . |
Create multiple Pivots based on list of parameters
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 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 (D2:D8) 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 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 = "" |
| All times are GMT +1. The time now is 06:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com