Macro copies worksheets and moves them to new workbook, causes macro reassignment?

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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,224,598
Messages
6,179,818
Members
452,946
Latest member
JoseDavid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top