A Good Morning to All:
I am working in Excel 2010. I created a workbook for distribution where the user will make a copy of the workbook posted in a central location, then paste the copy a local area of their own choosing.
With the workbook I have several functions which work the same. I am pulling data from an Oracle system (VBA string) and writing it to a sheet ("Table1_Sheet" containing the named range "tbl_Table1"). I then manually created a pivot table in a second sheet (PivotTbl1_Sheet containing the named range "pvt_Table1"). On subsequent Oracle downloads, which I trigger by clicking on a button on Table1_Sheet, I refreshed the pivot using the following line of code:
ActiveWorkbook.Worksheets("PivotTbl1_Sheet").PivotTables("pvt_Table1").PivotCache.Refresh
This worked so well that I used it to download, create table, refresh pivot for several different datasets. I then started to make copies of the workbook and here's the rub. In copies, the named table is local, but when I try to refresh the pivot cache the data source cannot be located. When I go to the sheet where the pivot is and look at the datasource, the pivot is pointing back--not to the sheet that I copied from--but to the sheet it was originally created on.
I don't know what to do. What is the nature of pivot cache? Do I need to "set" something?
Any suggetions will be most gratefully received. I am self taught knowing some things well while also being unaware of others.
I am working in Excel 2010. I created a workbook for distribution where the user will make a copy of the workbook posted in a central location, then paste the copy a local area of their own choosing.
With the workbook I have several functions which work the same. I am pulling data from an Oracle system (VBA string) and writing it to a sheet ("Table1_Sheet" containing the named range "tbl_Table1"). I then manually created a pivot table in a second sheet (PivotTbl1_Sheet containing the named range "pvt_Table1"). On subsequent Oracle downloads, which I trigger by clicking on a button on Table1_Sheet, I refreshed the pivot using the following line of code:
ActiveWorkbook.Worksheets("PivotTbl1_Sheet").PivotTables("pvt_Table1").PivotCache.Refresh
This worked so well that I used it to download, create table, refresh pivot for several different datasets. I then started to make copies of the workbook and here's the rub. In copies, the named table is local, but when I try to refresh the pivot cache the data source cannot be located. When I go to the sheet where the pivot is and look at the datasource, the pivot is pointing back--not to the sheet that I copied from--but to the sheet it was originally created on.
I don't know what to do. What is the nature of pivot cache? Do I need to "set" something?
Any suggetions will be most gratefully received. I am self taught knowing some things well while also being unaware of others.
