Source Data For Pivot Table - VBA

Hi mate,

From the help file it appears as though the SourceData property needs to be specified as a cell reference rather than a range object. I tried this first of all:-

Sheets(1).PivotTables(1).PivotCache.SourceData = "Sheet1!A5:T40000"

but that produced an error. I then tried:-

Sheets(1).PivotTables(1).PivotCache.SourceData = "Sheet1!R5C1:R40000C20"

and that worked. I ended up using this code (meaning that you can avoid hard-coding the reference):-


Any good?

Dan

Good thinking Dan, however Im getting a 1004 Application-defined or object-defined error now.

I noticed that the sheet name was enclosed in single quotes. My latest effort is this with the same error...

Bloody pivot tables. Anymore ideas?

Strange! Your code works OK for me in my sample workbook. What is the sheet name of your data source? Also, are there headings in every cell of the range A5:T5?

Dan

For some reason its working now, although I didnt change the code (just closed and re-opened the workbook). Maybe my threat to throw the PC out the window from the 8th floor did the trick.

Thanks Dan

Sheets(1).PivotTables(1).PivotCache.SourceData = "'" & Sheets(2).Name & "'!" & Range("A5:T40000").Address(ReferenceStyle:=xlR1C1)

This will make range reference R1c1 style

Try below it worked for me.
Sheets(1).PivotTables(1).PivotCache.SourceData = "'" & Sheets(2).Name & "'!" & Range("A5:T40000").Address