Pivot Tables with VBA

[Matt]

Hi all

I'm trying to produce a pivot table from using a range variable as the source data. This isn't working. Does anybody know how to create a pivot table from a range variable.

Hope someone can help

Thanks

Matt


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=BasebreakRange).CreatePivotTable TableDestination:="", _
TableName:="PivotTable2"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable2").SmallGrid = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="CATEGORY3", _
ColumnFields:="CATEGORY4", PageFields:="CATEGORY2"
With ActiveSheet.PivotTables("PivotTable2").PivotFields("DATA1")
.Orientation = xlDataField
.Caption = "Sum of DATA1"
.Function = xlSum
End With

 

[Juan Pablo González]

What is BasebreakRange ?

 

[Matt]

Juan

BasebreakRange = range("A1:H32")

 

[Juan Pablo González]

The PivotTable expects a range as that parameter, you're passing a Variant to it, that's probably what's breaking it.