Try to search your question here, if you can't find : Ask Any Question Now ?

Is there a method or property (related to PivotCache / PivotCaches) to identify why a pivotcache cannot be created?

HomeCategory: stackoverflowIs there a method or property (related to PivotCache / PivotCaches) to identify why a pivotcache cannot be created?
Avatarbhawya asked 5 months ago

I’m using XLS VBA code to create pivot tables based on named ranges.
As I’m getting an error code while invoking the CreatePivotTable, I noticed that the PivotCache never has been created. I cannot figure out the reason. Is there a way to deep-dive into PivotCaches.Create errors?

I already tried to define SourceData

  • with named range
  • with absolute references
  • with relative references
  • one step-by-step replacing each item with its string value

various declarations (wBk as Workboook, wSh as WorkSheet, kchPivot as PivotCache, piPivot as PivotTable, DataSourceRge as Range, DataSourceString as String …)

Set wBk = ActiveWorkbook
Set wSh = wBk.Sheets("MP_1_30")
Set DataSourceRge = Range("MP_1_30_Type_Ctl")

A) this does not work

Set kchPivot = wBk.PivotCaches.Create (xlDataBase, DataSourceRge)

B) this doesn’t work either

DataSourceString  = wSh.Name & "!" & _ 
DataSourceRge.Address(ReferenceStyle:=xlR1C1)

Set kchPivot = wBk.PivotCache.Create (xlDataBase, DataSourceString)

IMPORTANT: DataSourceRge is a ONE column range (nb: all the ranges for which I want to automate the creation of pivot tables are 1 column-wide)

I place the following control to ascertain the creation of the PivotCache

MsgBox wBk.PivotCaches.Count

I always receive “0” (nil) but would expect “1”

1 Answers
Best Answer
AvatarAmit answered 5 months ago
Your Answer

16 + 15 =

Popular Tags

WP Facebook Auto Publish Powered By : XYZScripts.com