VBA: Dynaically update a PivotTable range

[dha17]

Hi,

The following code works when changing the range of my PivotTables:

    Dim PC As PivotCache
    On Error Resume Next
    For Each PC In ThisWorkbook.PivotCaches
        PC.SourceData = "Data!R1C1:R100C5"
    Next PC

However, I am trying to modify the range dynamically using the CurrentRegion property, but cannot get it to work. And when I perform some debugging, I am not getting an address (or anything) returned:

    Dim PC As PivotCache
    On Error Resume Next
    For Each PC In ThisWorkbook.PivotCaches
        PC.SourceData = Worksheets("Data").Range("A1").CurrentRegion.Address
    Next PC

Any ideas?

Denny

 

[scifibum]

Hi,

Is your sheet protected? Excel help says "This property cannot be used on a protected worksheet."

 

[dha17]

Yes, my worksheet was protected. The CurrentRegion property now seems to work, however, my dynamic update does not work.

It appears that the CurrentRegion.Address property returns A1 notation, and I am only able to assign the SourceData using R1C1 notation. For example,

PC.SourceData = "Data!R1C1:R100C5" works
PC.SourceData = "Data!A1:E100" does not work

So, how to I convert my CurrentRegion.Address to R1C1... if that is the problem.

 

[dha17]

After further testing, I was able to create a statement that produces the exact same string as the statement that does work.

That is:

PC.SourceData = "Data!" & Worksheets("Data").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)

returns the same string as:

PC.SourceData = "Data!R1C1:R100C5"

However, only the latter statement works in modifying the PivotTable range...

 

[scifibum]

I tested with a similar concatenated string, and it worked for me:

Sub thing()
    Dim PC As PivotCache
    
    For Each PC In ThisWorkbook.PivotCaches
        PC.SourceData = "Sheet1!" & Worksheets("Sheet1").Range("C4").CurrentRegion.Address
    Next PC
    
End Sub

Hate to ask, but might you have re-protected your worksheet by the time you tested this? If not, is there an error message? (You'll need to remove or comment out the "On error resume next" statement to determine this)

 

[dha17]

I double checked and made sure that my worksheets were all unlocked. I was still not able to get the dynamic range to work properly and no error messages. What I found strange is that the formula used to set the range dynamically and the formula used to hard code the range are: PC.SourceData = "the exact same string".

Anyway, I did start up a new workbook and created sample data on sheet1 and a PivotTable on sheet two. I copied the Sub (thing) in the above message and ran it. It produced the following error:

Run-time error '1004':

The PivotTable field name is not valid. To create a PivotTable report, you must use the data that is organized as a list with labelled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.

I received the same error when I hardcoded the string in A1 notation. However, when I used R1C1 notation or changed the dynamic formula by adding (ReferenceStyle:=xlR1C1) after Address, then everything worked perfectly.

So, I am concerned why I had to add (ReferenceStyle:=xlR1C1) and why I cannot get this to work in my main spreadsheet. I will continue testing and perhaps build my spreadsheet again from scratch.

Denny
Windows XP, Office 2003