Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate Pivot Creation
Absolutely! Turn on the macro recorder and then create your Pivot Table.
Assign this to a Control Button to run it when needed. I'll provide a sample of code that I use, but please understand that your code will be quite different...I provide it only as a guideline... Sub PivotTableInputs() Sheets("MergeSheet").Select Cells.Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ Sheets("MergeSheet").Range("A1").CurrentRegion).Cr eatePivotTable _ TableDestination:="", TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion10 With ActiveSheet.PivotTables("PivotTable1").PivotFields ("State") .PivotItems("(blank)").Visible = False End With With ActiveSheet.PivotTables("PivotTable1").PivotFields (Sheets("Summary").Range("C5").Value) .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _ "PivotTable1").PivotFields(Sheets("Summary").Range ("C5").Value), "Count of ", xlCount With ActiveSheet.PivotTables("PivotTable1").PivotFields (Sheets("Summary").Range("C5").Value) .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("PivotTable1").PivotFields (Sheets("Summary").Range("C5").Value).AutoSort _ xlDescending, "Count of " ActiveSheet.Name = Range("MRG").Value & " - Pivot" Charts.Add With ActiveChart.ChartGroups(1) .Overlap = 100 .GapWidth = 150 .HasSeriesLines = False .VaryByCategories = False ActiveChart.ChartTitle.Select Selection.Text = "=Summary!R5C3" 'ActiveChart.ChartArea.Select ActiveSheet.Name = Range("MRG").Value & " - Chart" End With Selection.Text = "=Summary!R5C3" ActiveChart.ChartArea.Select End Sub The most important part is this: Cells.Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ Sheets("MergeSheet").Range("A1").CurrentRegion).Cr eatePivotTable _ TableDestination:="", TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion10 That is how you select the entire range of input data for the Pivot Table. Regards, Ryan--- -- RyGuy "acss" wrote: Not very familiar with macros in excell but here goes. I need to automate excel steps done monthly. I use a template workbook containing two sheets that is filled with data during the month then a pivot table is created for each sheet . Can the steps in creating the pivots be automated? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate Pivot Creation
Thanks. In this workbook, there are two worksheets with each one needing
their own pivot table so in the end result is a workbook with 2 sheets of data and two pivots. Do i use the recorder twice or do i begin recording and perform the pivots for both sheets at the same time? "ryguy7272" wrote: Absolutely! Turn on the macro recorder and then create your Pivot Table. Assign this to a Control Button to run it when needed. I'll provide a sample of code that I use, but please understand that your code will be quite different...I provide it only as a guideline... Sub PivotTableInputs() Sheets("MergeSheet").Select Cells.Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ Sheets("MergeSheet").Range("A1").CurrentRegion).Cr eatePivotTable _ TableDestination:="", TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion10 With ActiveSheet.PivotTables("PivotTable1").PivotFields ("State") .PivotItems("(blank)").Visible = False End With With ActiveSheet.PivotTables("PivotTable1").PivotFields (Sheets("Summary").Range("C5").Value) .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _ "PivotTable1").PivotFields(Sheets("Summary").Range ("C5").Value), "Count of ", xlCount With ActiveSheet.PivotTables("PivotTable1").PivotFields (Sheets("Summary").Range("C5").Value) .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("PivotTable1").PivotFields (Sheets("Summary").Range("C5").Value).AutoSort _ xlDescending, "Count of " ActiveSheet.Name = Range("MRG").Value & " - Pivot" Charts.Add With ActiveChart.ChartGroups(1) .Overlap = 100 .GapWidth = 150 .HasSeriesLines = False .VaryByCategories = False ActiveChart.ChartTitle.Select Selection.Text = "=Summary!R5C3" 'ActiveChart.ChartArea.Select ActiveSheet.Name = Range("MRG").Value & " - Chart" End With Selection.Text = "=Summary!R5C3" ActiveChart.ChartArea.Select End Sub The most important part is this: Cells.Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ Sheets("MergeSheet").Range("A1").CurrentRegion).Cr eatePivotTable _ TableDestination:="", TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion10 That is how you select the entire range of input data for the Pivot Table. Regards, Ryan--- -- RyGuy "acss" wrote: Not very familiar with macros in excell but here goes. I need to automate excel steps done monthly. I use a template workbook containing two sheets that is filled with data during the month then a pivot table is created for each sheet . Can the steps in creating the pivots be automated? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking to automate the creation of an org chart from a spreadshee | Excel Discussion (Misc queries) | |||
Automate PDF creation | Excel Programming | |||
automate creation of sheets in excel | Excel Worksheet Functions | |||
HOW DO I AUTOMATE CREATION OF JOB SHEETS? | Excel Programming | |||
Automate PDF file creation in Excel code | Excel Programming |