Cannot update PivotTable Source Data when copy from other workbook

[Steph]

Hi -

Having an annoying problem. I have an Excel workbook which is serving as a
"template" to create Excel reports. Basically, I have the template
worksheets in this workbook - I query a database using this template, and
once I get the data - I copy the worksheets from the template workbook into
a new workbook, so that the template workbook isn't changed.

I have a PivotTable defined on one of the template worksheets. When I copy
this PivotTable to the new workbook, the PivotTable still thinks the source
data is in the template workbook. When I try to change the source data using
this line of code - where "Releases" is the Pivot Table, and rg is a Range
in the new workbook -

ActiveSheet.PivotTables("Releases").SourceData = "'can-com ECP'!" &
rg.Address

I get the following error:

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

In debug mode - when I look at "ActiveSheet.PivotTables" - it clearly sees
the Pivot Table, but for some reason isn't allowing me to change the source
data. I would like not to have to recreate the Pivot Tables each time ...

Any ideas?

Thanks,

Stephanie

 

[Steph]

Think I solved my own issue - it had to do with blank cells in the first row
of the data source -

http://support.microsoft.com/default.aspx?scid=kb;en-us;213955