I'm trying to create a pivot table using variables (my first time coding pivot tables). On Excel 2002, the following works (NOTE: I've named the data range in the source worksheet = "TesterDB"):
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
        SourceData:=SourceWks.Range("TesterDB")). _
        CreatePivotTable TableDestination:= _
        DestWks.Range("A1"), TableName:="MyFirstPivot"

Unfortunately, my company is using Excel 2000 so I've modified the above to:
    MySource = RootDirectory & "[Source.xls]Sheet3!R1C1:R1470C17"
    MyDest = "Sheet1!R1C1"
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
        SourceData:=MySource). _
        CreatePivotTable TableDestination:= _
        MyDest, TableName:=PTTableName

This doesn't allow me to change the destination of the pivot table (it gets dumped into the source spreadsheet). So I have 3 different workbooks involved: the current active workbook, a separate source workbook, and a separate destination workbook. There must be a way to use variables to access these...Could someone please direct me?

Thank you,
DMW
Toronto, Canada