pivot cache in VBA

B

BenKoeppen

Hi,
I want to create a pivot cache using VBA. If I write

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=
_
"A1:AC3167").CreatePivotTable TableDestination:="", _
TableName:="myPivotTable",
DefaultVersion:=xlPivotTableVersion10

it works. But if I write

myLastRow = Range("A1").End(xlDown).Row
myLastColumn = Range("A1").End(xlToRight).column
myRange = Range(Cells(1, 1), Cells(myLastRow, myLastColumn))

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
myRange).CreatePivotTable TableDestination:="", _
TableName:="myPivotTable",
DefaultVersion:=xlPivotTableVersion10

I get an "Type Mismatch" error. I tried various structures from for
the myRange, but none worked.

Any help apreciated.

Ben
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot Table Macro with dynamic source data 1
Why R1C1 range parameters in VBA Pivot Table setup/ 13
Pivot table code question 2
Build Pivot Table with VBA; Keeps Throwing an Error 1
Avoiding an extra sheet when creating a pivot table 3
Pivot table Macro help!!! 3
PivotCaches.add Difficulties 0
Excel TableDestination As String? 1

Top