Pivot Table source data range (VBA)

[rdw72777]

I'm so annoyed. I have tried numerous attempts at this using code others have posted and this simply won't work.

I'm simply trying to adjust my data range for a pre-existing pivot table to include the appropriate range. I have hard-coded the range (I was using variables for the rows but nothign works so i gave up). Even this code, with a defined data range, doesn't work:

Sheets("Other Projects Detail").PivotTables("PivotTable1").SourceData = Sheets("ET").Range("J1:AO820").Address(True, True, xlR1C1, True)

I've tried other variations but keep getting a "Run time error 9: Subscript out of range error", but I really think the code should work. If I update the pivot table manually with this data range it works fine, so I don't think its the pivot table itself that's the issue.

I assume I'm missing something easy in my code, but can't see what it is.

 

[GlennUK]

try:

= "ET!" & Range("J1:AO820").Address(True, True, xlR1C1, False)

 

[niceguy_jay]

what is the new range that you want to use ??

 

[rdw72777]

Jay, the range listed in the code is the new range. The pivot in the template is set to a different range.

 

[rdw72777]

GlennUK, I tried your fix but it agve a different error (that to be honest closed down my Excel so I'm going to keeop playing with things with the "Sheets("ET")> syntax)

 

[rdw72777]

Actually, I just tried changing the sheet name itself and then the sheetname in the code and it worked, so maybe something just got hung up and it didn't liike the sheetname. Regardless, it works now.