Using VBA to change Pivot Table data source
 

I have a workbook with 20 Pivot Tables that I need to update the last row
referenced by the data set for the Pivot Table via VBA. I have the last row
number by using:

NumberOfEntries = _
Worksheets("Raw_Data").Cells(Rows.Count, "A").End(xlUp).Row

.... (more code) and after loading updated data I want to programmatically
update the data set used to include all rows in the worksheet. Problem is
that Excel hard codes the path to the file name in the SourceData:= field:

Sheets("Usage by PMT PT").Select
ActiveSheet.PivotTables("PivotTable15").ChangePivo tCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="C:\[MyWorkbook.xlsm]Organization!R5C1:R860C17", _
Version:=xlPivotTableVersion12)

I've tried using ActiveWorkbook.Path and building a path string to where
ever the workbook might be but that doesn't seem to work. All I need to do
is update the total number of rows involved in the data set as the columns
don't change.

Any ideas on how to do this? I searched the discussion group already and
found nothing pertaining to this type of problem. Also, I am using 2007 with
latest service packs installed.

Thanks,

Bryan44