Creating a Pivot table

  • Thread starter Thread starter Jodie
  • Start date Start date
J

Jodie

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?
 
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?
Click to expand...

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:=xlDatabase,
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").AddDataField
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:=xlDatabase,
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").AddDataField
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
 
' Assuming your data starts in column A, with headers in row 1
' and the column has no blanks
' Once the file is open

Sheet_Name = ActiveSheet.Name
Last_Row = ActiveSheet.Range("A65536").End(xlUp).Row
Your_Last_Column = 10 ' amaned this to the column number of your last
column
Pivot_Name = "YOUR PIVOT TABLE NAME"
Source_Range = "'" + Sheet_Name + "'!R1C1:R" + CStr(Last_Row) + "C" +
CStr(Your_Last_Column)
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Source_Range).CreatePivotTable TableDestination:= _
"", TableName:=Pivot_Name, DefaultVersion:=xlPivotTableVersion10

I am assuming that you've got the rest of the pivot routine and it's just
the range that's an issue.
 
Back
Top