I am trying to write a macro that adds a pivot table to the workbook,
however, I need to be able to use this macro in any workbook.
The work books only have 1 sheet and all sheets vary in size, but all must
have a pivot table displaying that workbook data. The recorded Macro looks
like this but the source data (WL Build 4BCT) and the range will also differ
by each sheet I need to run this on.
Range("A2").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"WL BUILD 4 BCT!R1C1:R3702C20", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="Sheet1!R3C1",
TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion12
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("AA_Unit_Name")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("OH"), "Sum of OH", xlSum
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pivot1"
Range("A2").Select
End Sub
Any help would be appreciated.