selecting cells to produce a pivot table

  • Thread starter Thread starter Anita
  • Start date Start date
A

Anita

I have created a macro that inserts a pivot table using some data on sheet1.
I thought that the macro would select the entire list of data to produce the
pivot each time it is run but I notice in the code that it selects an
absolute range and the range will be different each time I run the macro.
How can I change the code to tell Excel to select the populated cells in the
list from A1 to the end? Using v2003 - thanks

PS - I also made a mistake recording the macro and meant for one of the
value fields in the pivot table to perform a sum rather than a count is this
easy to amend using code?

Anita
 
Anita,

Post your code - the changes required depend on the specifics of your
current code.


HTH,
Bernie
MS Excel MVP
 
Here's the code thanks...

Rows("1:6").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Range("H1").Select
ActiveCell.FormulaR1C1 = "Order older than inv?"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>RC[-5],1,0)"
Selection.AutoFill Destination:=Range("H2:H5570")
Range("H2:H5570").Select
Range("G2").Select
ActiveWindow.LargeScroll ToRight:=1
Range("L1").Select
ActiveCell.FormulaR1C1 = "Cost Centre"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Division"
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R5570C13").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
"Cost Centre", "Division", "Supplier Title", "Data")
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Invoice Number")
.Orientation = xlDataField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Order older than
inv?"). _
Orientation = xlDataField
End Sub
 
Anita,

Change

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R5570C13").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10


to

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R" & Worksheets("Sheet1").Cells(Rows.Count,
1).End(xlUp).Row _
& "C13").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion10

HTH,
Bernie
MS Excel MVP


Anita said:
Here's the code thanks...

Rows("1:6").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Range("H1").Select
ActiveCell.FormulaR1C1 = "Order older than inv?"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>RC[-5],1,0)"
Selection.AutoFill Destination:=Range("H2:H5570")
Range("H2:H5570").Select
Range("G2").Select
ActiveWindow.LargeScroll ToRight:=1
Range("L1").Select
ActiveCell.FormulaR1C1 = "Cost Centre"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Division"
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R5570C13").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
"Cost Centre", "Division", "Supplier Title", "Data")
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Invoice
Number")
.Orientation = xlDataField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Order older than
inv?"). _
Orientation = xlDataField
End Sub



Bernie Deitrick said:
Anita,

Post your code - the changes required depend on the specifics of your
current code.


HTH,
Bernie
MS Excel MVP





.
Click to expand...
Click to expand...
 
Thanks v much for your help - I'll give this a try when I'm next in work and
have access to some raw data to test it...

Bernie Deitrick said:
Anita,

Change

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R5570C13").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10


to

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R" & Worksheets("Sheet1").Cells(Rows.Count,
1).End(xlUp).Row _
& "C13").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion10

HTH,
Bernie
MS Excel MVP


Anita said:
Here's the code thanks...

Rows("1:6").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Range("H1").Select
ActiveCell.FormulaR1C1 = "Order older than inv?"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>RC[-5],1,0)"
Selection.AutoFill Destination:=Range("H2:H5570")
Range("H2:H5570").Select
Range("G2").Select
ActiveWindow.LargeScroll ToRight:=1
Range("L1").Select
ActiveCell.FormulaR1C1 = "Cost Centre"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Division"
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R5570C13").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
"Cost Centre", "Division", "Supplier Title", "Data")
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Invoice
Number")
.Orientation = xlDataField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Order older than
inv?"). _
Orientation = xlDataField
End Sub



Bernie Deitrick said:
Anita,

Post your code - the changes required depend on the specifics of your
current code.


HTH,
Bernie
MS Excel MVP




I have created a macro that inserts a pivot table using some data on
sheet1.
I thought that the macro would select the entire list of data to
produce
the
pivot each time it is run but I notice in the code that it selects an
absolute range and the range will be different each time I run the
macro.
How can I change the code to tell Excel to select the populated cells
in
the
list from A1 to the end? Using v2003 - thanks

PS - I also made a mistake recording the macro and meant for one of the
value fields in the pivot table to perform a sum rather than a count is
this
easy to amend using code?

Anita


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top