David Schaleger
Board Regular
- Joined
- Dec 6, 2010
- Messages
- 55
When copying a Report sheet with pivot tables to another workbook, ranges from the source workbook that are not associated with the Report sheet are also copied. This results in a “can’t update links in workbook” error upon opening the WB with the copied sheet, which resides on a networked drive. These files were created in Excel 2007, but recently converted to Excel 2013.
Sheet “Report” in source WB has pivot tables based on a data table in sheet “DataA”. Sheet “Lookup” has 3 named ranges which are used in formulas on sheets “DataB” and “DataC” to look up and return values from data tables and named ranges on sheet Lookup back to DataB and DataC; no association with sheets Report or DataA.
VBA code on sheet Report refreshes the pivot tables, and then copies sheet Report to an existing WB with a previously copied sheet Report, which it then deletes the existing sheet Report, leaving only the just copied sheet Report. Opening the copied file generates the aforementioned error, because the copied WB, which is on a network drive, is referencing the source WB, which is on my local PC (the copied ranges have paths to source file name, but looking on the NW drive). I can manually delete the ranges from the copied workbook, alleviating the problem until the ranges return upon next VBA update run. I can also duplicate this copying-the-ranges problem by manually copying sheet Report to a new WB.
I don’t understand why copying the Report sheet also copies named ranges that aren’t associated with either the Report sheet or the DataA sheet that the Report sheet is pulling from. These ranges are on the Lookup sheet, and are used in formulas on DataB and DataC sheets. This problem (along with SO many more) was noticed sometime after converting from Excel 2007 to 2013.
This isn't the only time I've run into this, or similar, problems. Why is this happening? How do I prevent it from occurring?
Sheet “Report” in source WB has pivot tables based on a data table in sheet “DataA”. Sheet “Lookup” has 3 named ranges which are used in formulas on sheets “DataB” and “DataC” to look up and return values from data tables and named ranges on sheet Lookup back to DataB and DataC; no association with sheets Report or DataA.
VBA code on sheet Report refreshes the pivot tables, and then copies sheet Report to an existing WB with a previously copied sheet Report, which it then deletes the existing sheet Report, leaving only the just copied sheet Report. Opening the copied file generates the aforementioned error, because the copied WB, which is on a network drive, is referencing the source WB, which is on my local PC (the copied ranges have paths to source file name, but looking on the NW drive). I can manually delete the ranges from the copied workbook, alleviating the problem until the ranges return upon next VBA update run. I can also duplicate this copying-the-ranges problem by manually copying sheet Report to a new WB.
I don’t understand why copying the Report sheet also copies named ranges that aren’t associated with either the Report sheet or the DataA sheet that the Report sheet is pulling from. These ranges are on the Lookup sheet, and are used in formulas on DataB and DataC sheets. This problem (along with SO many more) was noticed sometime after converting from Excel 2007 to 2013.
This isn't the only time I've run into this, or similar, problems. Why is this happening? How do I prevent it from occurring?
