Philip_Trick
New Member
- Joined
- Jul 27, 2011
- Messages
- 5
Sorry for the long title, I've got a problem that's a little difficult to describe.
I have a workbook with a series of buttons, ~200 shapes with macros assigned to them. In this workbook, one of these buttons creates a copy of three specific sheets within the same workbook, converts all of the cells to values and performs some formatting adjustments (print ranges, etc), checks for the existence of a certain workbook and either creates that workbook or opens that workbook before moving the copied worksheets into that workbook.
My problem occurs immediately after executing the following line:
currentWorkbook.Sheets(sheetArray).Move After:=targetWorkbook.Sheets(1)
Once this line is executed, every shape in my original workbook points to the new "targetWorkbook"!MacroName instead of "currentWorkbook"!MacroName.
If I replace the "Move" line with the following two lines...
currentWorkbook.Sheets(sheetArray).Copy After:=targetWorkbook.Sheets(1)
currentWorkbook.Sheets(sheetArray).Delete
I no longer have the problem.
To add difficulty to this, I have tried this on two Windows 7 machines and it works on one of them while producing the error on the other.
Is anyone familiar with this problem, how I might fix it, or what's special about the move method that causes it?
Alternatively, does anyone know what the performance loss/gain is to using the "Copy" version vs the "Move" version?
Thanks very much!
Philip Trick
I have a workbook with a series of buttons, ~200 shapes with macros assigned to them. In this workbook, one of these buttons creates a copy of three specific sheets within the same workbook, converts all of the cells to values and performs some formatting adjustments (print ranges, etc), checks for the existence of a certain workbook and either creates that workbook or opens that workbook before moving the copied worksheets into that workbook.
My problem occurs immediately after executing the following line:
currentWorkbook.Sheets(sheetArray).Move After:=targetWorkbook.Sheets(1)
Once this line is executed, every shape in my original workbook points to the new "targetWorkbook"!MacroName instead of "currentWorkbook"!MacroName.
If I replace the "Move" line with the following two lines...
currentWorkbook.Sheets(sheetArray).Copy After:=targetWorkbook.Sheets(1)
currentWorkbook.Sheets(sheetArray).Delete
I no longer have the problem.
To add difficulty to this, I have tried this on two Windows 7 machines and it works on one of them while producing the error on the other.
Is anyone familiar with this problem, how I might fix it, or what's special about the move method that causes it?
Alternatively, does anyone know what the performance loss/gain is to using the "Copy" version vs the "Move" version?
Thanks very much!
Philip Trick