A
allenx
I am aware of one VBA method for copying a worksheet and it's local macros
to a new workbook. In short, it uses Export and Import commands that
temporarily dis-associate the macro from the worksheet.
The problem arises if the worksheet uses macro hot spots. When the
spreadsheet and its macros are reunited in the new workbook, the hot spot
assignment (pointers) are not automatically updated. Thus they do not find
the local macro because the path has changed. Keyboard activation of the
macros and drop-down menus work fine, so the problem is with hot spots and
not the macros
I've tried numerous ideas to automatically relink the hot spots and macros
via another macro. But worksheet_activate(), error messages and other Excel
events interact with the copying macro and haven't worked.
Is there another general VBA technique for copying a worksheet and its
associated macros?
..
to a new workbook. In short, it uses Export and Import commands that
temporarily dis-associate the macro from the worksheet.
The problem arises if the worksheet uses macro hot spots. When the
spreadsheet and its macros are reunited in the new workbook, the hot spot
assignment (pointers) are not automatically updated. Thus they do not find
the local macro because the path has changed. Keyboard activation of the
macros and drop-down menus work fine, so the problem is with hot spots and
not the macros
I've tried numerous ideas to automatically relink the hot spots and macros
via another macro. But worksheet_activate(), error messages and other Excel
events interact with the copying macro and haven't worked.
Is there another general VBA technique for copying a worksheet and its
associated macros?
..