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:=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---
>
> --
> 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?
|
Similar Threads
