Automate Pivot Creation

[acss]

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?

 

[ryguy7272]

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:=xlDatabase, SourceData:= _
Sheets("MergeSheet").Range("A1").CurrentRegion).CreatePivotTable _
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").AddDataField
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:=xlDatabase, SourceData:= _
Sheets("MergeSheet").Range("A1").CurrentRegion).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

That is how you select the entire range of input data for the Pivot Table.

Regards,
Ryan---

 

[acss]

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?