On Nov 20, 1:19*pm, Jodie wrote:
I am trying to write a macro to create a pivot table to use in multiple files
where each file will contain the same headers and number of columns. *
However, the number of rows will vary in each file. *Each file has one sheet
and all of these sheets have a different name. *Can anyone please help?
--
Thank you, Jodie

Jodie,

Here is an example set of data that I used.

1 A B C
2 Product Month Sales
3 Animal Jan-09 50
4 Vegetable Jan-09 20
5 Mineral Jan-09 10
6 Animal Feb-09 8
7 Vegetable Feb-09 50
8 Mineral Feb-09 20
9 Animal Mar-09 6
10 Vegetable Mar-09 10
11 Mineral Mar-09 18

Record a macro to create your pivot table. Here is the macro that I
recorded for this data set.

Sub Macro()

Range("A1:C10").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Sheet1!R1C1:R10C3").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells
(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Product")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Month")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Sales"), "Sum of Sales", xlSum

End Sub

Now, add code to the top of this to find the last row (For this
example I'm using column A) , change the Range Select statment to
select A1 and change the SourceData statement to use the rw variable
to detemine the last row.

Sub Macro()

Dim rw As Integer
' get the LAST cell
rw = Range("A65000").End(xlUp).Row

Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Sheet1!R1C1:R" & rw & "C3").CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells
(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Product")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Month")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Sales"), "Sum of Sales", xlSum

End Sub

If you have trouble with this. Record your pivot table macro and post
it.

Chuck