PDA

View Full Version : Creating Pivot tables



Djblois
08-22-2006, 08:59 AM
here is my code:

Dim pvt1 As Worksheet
Dim ptCache As PivotCache
Dim pt As PivotTable
Dim pRange As Range
Dim finalRow As Long
Set detail = Worksheets("detail")

'Create sheet
Set pvt1 = Worksheets.Add

'Create Cust-Prod Pivot table by month
finalRow = detail.Cells(65536, 1).End(xlUp).Row
Set pRange = detail.Cells(1, 1).Resize(finalRow, 20)
Set ptCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=pRange.Address)
Set pt = ptCache.CreatePivotTable(TableDestination:=pvt1.Range("A1"), _
TableName:="CustProd")

I am getting an error on the last line of code saying "Method 'CreatePivot Table' of object 'PivotCache' failed. Does anybody know what the error is?

matthewspatrick
08-22-2006, 05:25 PM
Did you write that up yourself, or modify output from the macro recorder?

I have to admit, I find PT coding so abstruse that I almost always record my steps, and then clean up the recorded code.

geekgirlau
08-22-2006, 08:22 PM
I find the simplest solution is to have a dynamic range name for the data. This way the range expands automatically according to the number of rows. Then all your macro has to do is refresh the pivot table.

You define the range as

"=OFFSET(Details!$A$1,0,0,COUNTA(Details!$A:$A),20)"