Dynamic Pivot Table Source

[Exer]

Hi, I've had a look around the forums and can't find a solution to my problem, I hope somebody can help

I have a list of data in workbook "Data" and I want to have a pivot table in workbook "Pivot" pick up this data.

The problem is I want the source of the pivot table to change according to how many rows in the data sheet have values.

My initial solution was to use a named data range, but that doesn't work unless both workbooks are open.

My 2nd solution was to have a cell in the pivot table workbook return the number of rows in the data workbook with values:

=MATCH(1E+306,'[Data.xls]Sheet1'!$A:$A,1)

This works, so now I tried to have the pivot tables source range updating according to this value and I got stuck. The code im trying to use is:

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _         "'C:\[Data.xls]Sheet1'!R1C1:R[COLOR=Red]3[/COLOR]C13"

I need the number I've highlighted in red above to change according to value of a cell in the same worksheet.

Thanks in advance for any help

 

[Exer]

Any ideas?

I get the feeling using code from the wizard was a bad idea but I didn't know how else to change the source of a pivot table.