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.

This is not too hard. Use this batch processing code:
http://www.rondebruin.nl/copy4.htm

As Ron states, you must replace the code in red with your macro. Also, how
do you find the end of the used range for those pivots? Take a look at this
and see if it helps:
Sheets("Sheet1").Select
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
Sheets("Sheet1").Range("A1").CurrentRegion).Create PivotTable _
TableDestination:=Sheets("Pivot-Sheet").Range("A3"),
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

Range("A1").CurrentRegion) -- this will ensure that you find the end of the
used range so all data is included in the pivot tables.

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Evil with a K" wrote:

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.