Pivot Table Question 1

[willyboy58]

I created a pivot table by recording the macro. I did not stop to think that the next month’s data would not be of the same range size.

'the following is part of the original code produced by the recorded macro:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= "'Branch 3'!R1C1:R1646C5&quot.CreatePivotTable TableDestination:="", TableName:="PivotTable6"

Given that the range size can change, I changed my code to the following:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Activecell.CurrentRegion.Select).CreatePivotTable _
TableDestination:="", TableName:="PivotTable6"

The new code selects the proper range of cells as it should, but I get the message: “Invalid procedure call or argument.” In the code module area, the little arrow to the left is at the line “ TableDestination:="", TableName:="PivotTable6". What can I do to fix this?

If needed, the complete procedure follows:
Sheets("Branch 3&quot.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Activecell.CurrentRegion.Select).CreatePivotTable _
TableDestination:="", TableName:="PivotTable6"

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable6&quot.SmallGrid = False
With ActiveSheet.PivotTables("PivotTable6&quot.PivotFields("number&quot
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable6&quot.PivotFields("name&quot
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable6&quot.PivotFields("job&quot
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable6&quot.PivotFields("date&quot
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable6&quot.PivotFields("hours&quot
.Orientation = xlDataField
.Position = 1
End With
ActiveSheet.Name = "Pivot Table 3"
Application.CommandBars("PivotTable&quot.Visible = False
End Sub

TIA. Bill

 

[TonyJollans]

Hi willyboy58,

SourceData needs to be a range. Just use Activecell.CurrentRegion - i.e. without the .Select

Enjoy,
Tony

 

[willyboy58]

Tony,

Thanks for the input, but it didn't work. I now get the message "Method 'Add' of object 'PivotCaches' failed. The cells on the sheet were also not selected.

Bill

 

[SARUN]

This is a peice of code that I've been using for months

Dim mLstRow As Variant
Range("A1&quot.Select
Selection.End(xlDown).Select
mLstRow = ActiveCell.Row

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
ActiveSheet.Name & "!R1C1:R" & mLstRow & "C26", TableDestination:="", TableName:="PivotTable1"

You can try this with ... & "'Branch 3'!R1C1:R" & mLstRow & "C5"

Arun

"Whereever you go there are people who need you for what you can do..."

 

[TonyJollans]

Hi willyboy58,

My apologies. The SourceData parameter wants the range address as text, not the range itself, so use Activecell.CurrentRegion.Address instead.

If you want to have the range selected as well (although you don't need to, to create the pivot table) do that in a separate statement before (or after) building the pivot table.

Enjoy,
Tony

 

[willyboy58]

Thanks Tony!

I'm almost there!!