Pivot Table Source Data Question

[tthiele]

I have the following code which creates a pivot table:
_________________________________________

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"IntermediateCalculations!R19C15:R866C18").CreatePivotTable TableDestination _
:="'PivotTable'!R3C1", TableName:="CopyTable", DefaultVersion:=xlPivotTableVersion10
_________________________________________

Obviously, it will create a pivot table for data that resides in the range O15:R866. However, I want it to be able to create a pivot table for an expanding range.

I thought that the following modifications would work:
____________________________________________
str1 = "IntermediateCalculations!R19C15"
str2 = Sheet12.Range("O19").End(xlDown).End(xlToRight).Address(ReferenceStyle:=xlR1C1)
str3 = ""
str4 = str1 & str2 & str3

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=str4).CreatePivotTable TableDestination _
:="'PivotTable'!R3C1", TableName:="CopyTable", DefaultVersion:=xlPivotTableVersion10
____________________________________________

However, when I run this code, the following error occurs:

Run-time error '1004':
Application-defined or object-defined error.

Which seems to indicate to me that something is wrong with my use of strings to accomplish my goal.

Is there some other way to do this, or is there something wrong with my syntax that I can modify?

Thanks in advance,

Troy

 

[Jim North]

Which line are you getting the error on?

 

[tthiele]

The activeworkbook.pivotcaches.add line.

I am pretty sure it has to do with how I have the data source set up.

Thanks,

Troy

 

[Mark W.]

Create and use a Defined Name for your PivotTable's source data. Add new rows to your source data using a Data Form or have your Defined Name refer to a formula that recognizes additional rows.

 

[tthiele]

Thanks, but I tried that, and received the same set of errors. I am convinced that there is some special format of how that line is supposed to look. Any other troubleshooting ideas?

Thanks,

Troy

 

[duplinguy]

Try not using actual row & column references SourceData:= _
"IntermediateCalculations!R19C15:R866C18"

Try using this:
SourceData:=Selection.CurrentRegion