RunTime error 438 Can anyone see what is wrong with this code?

[sheppard26]

Hi,

I am trying to set the PivotCache SourceData to a value,which is a filename/path, in a specific cell within the workbook.

I have written the following code but receive a runtime error 438 when running it.

Dim Source As String
Dim PTCache As PivotCache
Source = ActiveWorkbook.Sheets("Macros").Range("B7").Value
Set PTCache = ActiveWorkbook.PivotCaches.create( _
SourceType:=xlDatabase, _
SourceData:=Source)


'Refresh Pivot Cache
Sheets("Spot Sales").PivotTables("Endur Source").PivotCache.Refresh

This Cache is specific to one of many pivottable within the workbook, I have not specified that this cache is relating to this pivottable, could this be the problem? All the other pivot tables are linked to this pivot in question.
I hope this makes sense.


PLEASE PLEASE help, this is driving me nuts!

 

[sheppard26]

But I thought when using this code when creating the cache it would have a link to the value in cell B7?

Source = ActiveWorkbook.Sheets("Macros").Range("B7").Value

PTCachetest = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=Source)

thanks

 

[RoryA]

Nope. Source is assigned the value of B7 at the time the code runs and that literal value is then used to build the cache.

 

[sheppard26]

ah.... Interesting....

AARGH! Now I have to come up with some ingenious idea to alter this SourceData property as you have said, I am not sure that I am capable of ingenious things!

Thanks for your help so far... I will work on this and prob come back to you...

One more thing, the code that worked perfectly to create cache and pivot etc is now giving me bugs... any idea why it would work perfectly for days and then just conk out, without any changes? VERY FRUSTRATING

Thanks again Rory!

 

[RoryA]

Not without knowing what bugs and on what lines. Could be referring to ranges without specifying sheets, missing field names, or any one of a hundred other things.