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?

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?