Refresh Pivot with larger cache

[Djblois]

I am trying to create code that will refresh a pivottable with a larger amount of date then what it originally had. This is the code that I have tried:


finalRow = Detail.Cells(Rows.Count, "B").End(xlUp).Offset(-1, 0).Row
finalColumn = Detail.Cells(1, 256).End(xlToLeft).Column
Set pRange = Detail.Cells(1, 1).Resize(finalRow, finalColumn)
Set ptCache = WB(1).PivotCaches.Add(SourceType:=xlDatabase, SourceData:=pRange.Address)
ActiveSheet.PivotTables("PivotTable").PivotCache.Refresh

Am I trying to accomplish this correctly?

[Djblois]

This is for another part of my add-in that I am creating.

[geekgirlau]

The simplest solution is to use a dynamic range for your data. That way you only need to refresh the cache. You define the range by selecting Insert | Name, and set "Refers To" as something like the following:


=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),<number of columns>)

[Djblois]

How do I do that with code?

[geekgirlau]

You shouldn't need to - if you define the range name for your pivot table source data as I've described above, and modify your pivot table to refer to the range name rather than the cell addresses, you only need the last line of your code.

I've attached a sample here - if you add some new records at the bottom of the "Data" sheet, all you need to do is refresh the pivot table. Because the pivot table gets the source data from the dynamic named range, you don't need to redefine the range.

[Djblois]

I just used the macro recorder to see how do I name a range. Here is what I got:


Range("A1:L30").Select
ActiveWorkbook.Names.Add Name:="Test", RefersToR1C1:="=Sheet1!R1C1:R30C12"

How do I make that dynamic?

[geekgirlau]

You don't need code to create a dynamic range - just follow the steps in post #3. Because the size of the range will adjust automatically to fit your data, the range only needs to be defined once, not each time you run the macro to refresh your pivot table.

[Djblois]

the amount of columns may also be different. In yours it looks like that is static. My ultimate goal is for a user to be able to use my add-in to create a pivottable and then in the future use my program to add new data to the cache and then refresh the pivot.

[geekgirlau]

To make the number of columns dynamic,


=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

[Djblois]

That looks closer to what I need geekgirl. The last thing I need is I do need to add it with code. The reason is it will be part of my add-in so when somebody adds more data to the detail sheet, they can then refresh their pivottables or reports as I call them. I am making this add-in for people who know nothing about excel. That is why my add-in does all the manual work for them.

This is how I start a Pivot table in my code right now:


finalRow = Detail.Cells(Rows.Count, "B").End(xlUp).Offset(-1, 0).Row
finalColumn = Detail.Cells(1, 256).End(xlToLeft).Column
Detail.Activate
Set pRange = Detail.Cells(1, 1).Resize(finalRow, finalColumn)
Set ptCache = WB(1).PivotCaches.Add(SourceType:=xlDatabase, SourceData:=pRange.Address)
Set PT = ptCache.CreatePivotTable(TableDestination:=pvt.Range("A1"), TableName:="PivotTable")
PT.RowGrand = False
PT.PrintTitles = True
PT.NullString = "0"
PT.DisplayErrorString = True

How would I change that?

[geekgirlau]

To set the range,



ActiveWorkbook.Names.Add Name:="pvtData", RefersToR1C1:= _
"=OFFSET(Detail!R1C1,0,0,COUNTA(Detail!C1),COUNTA(Detail!R1))"


You then use the range "pvtData" (or whatever you want to call it) as the source for your pivot table.

[Djblois]

I do that with the source data not the pivot? correct? If I am correct, how do I do that if the source data sheet is already created?

[geekgirlau]

All this does is create the range name "pvtData" - it depends on the source data sheet already being in existence, and will generate an error if it is not. Your pivot table should then use this range as its source.