Copy worksheets from a template then macro to reassign the macros

[itsthecheese]

Happy New Year All, I need your help with something I've really struggled with... plus I'm a little green w/VBA so please bear with me...

I am manually copying certain worksheets into existing workbooks. Everything transfers over ok with the exception of Autoshapes -

Issue:
Original Template/AutoShape #1 is an arrow assigned to a macro that, when selected, changes the formatting for the current row

(Then I run a macro to reformat the workbook for general changes (cell references, print parameters, etc.)

New Worksheet/AutoShapes copy over perfectly, however their macros *stay* assigned to the original Template - thus creating an unwanted "link"
(i.e.: 'Selection Summary-SAVE AS TEMPLATE.XLT'!Sheet19.A1_Teal)

I then have to right-click each of the AutoShapes in the workbook and re-assign the macros manually (from "This Workbook" ... i.e.: Sheet20.A1_Teal)

- it would be great if I had a macro to reassign the all AutoShape macros in one fell swoop (since their are multiple incidences of this) ...

I've tried to record the macro however it returns with:
ActiveSheet.Shapes("AutoShape 64").Select
Selection.OnAction = "Sheet19.A2_Red"
which obviously fails.

I know someone out there has the answer... thank you in advance for your feedback and great support.

-Ellie

drive slow...steer fast

 

[SkipVought]

Hi,

Copy the code from the module into the SHEET OBJECT of the sheet you are copying.

Once the code is in the sheet object, copying the sheet also copies the code.

Skip,

_[red][/red]

 

[itsthecheese]

Hi Skip...

Thanks for that but I was hoping for an automated macro procedure. Other folks will be filling in for me (sick days, dr. appts, etc.) and they don't know anything about vba... the exposure to catastrophies in my absence has me very insecure about providing instructions and access to copy the code in the back-end

Just in case I left out any details in my original communication I'll try to clarify - When I copy the worksheets all the good code moves over to the new workbook ... but the autoshapes themselves maintain their link to the original worksheet/original macro assignment...

(i.e. 'Selection Summary-SAVE AS TEMPLATE1'!Sheet19.A1_Teal)

then I have to right click each autoshape and manually reassign the macro from the old workbook/sheet to the macro for "this workbook".

(i.e. Sheet19.A1_Teal)

Is there no way to have a macro re-assign an autoshape from one workbook to code in a copied workbook. This provides the opportunity to only have to train the "copy from" and provide one macro to reformat the entire document while bypassing any access to the vba code itself.

??????
ActiveSheet.Shapes("AutoShape 43").Select
Assign.Macro = "Sheet19.A1_Teal"
??????

whew! hope I'm not giving you a gray hair... I totally appreciate your support and look forward to any advice you can provide.

Have a great day - Ellie

drive slow...steer fast

 

[SkipVought]

"...I was hoping for an automated macro procedure."

You do this ONCE.

Once the code is in the Sheet Object, whenever the Sheet is copied, so is the code.

Skip,

_[red][/red]

 

[itsthecheese]

Hi Skip....

I'm still learning/self-taught at VBA so perhaps I'm missing something obvious... please bear with me...

If I'm understanding your instructions correctly then I'm still lost - the code is/has always been in the sheet object (see the example code above) - it's never been in a module. All other macros in that (and every other) worksheet copy over "unlinked" so I can't figure out for the life of me why *only* the autoshapes on this one worksheet stay linked to the original template... Does it matter that they are not "Private"?

-Ellie




drive slow...steer fast

 

[itsthecheese]

Skip...

I don't want to assume anything but is your lack of response some sort of "message"? ... or is it simply an oversight?

If you don't plan on responding to my last message please let me know.

thank you,

Ellie



drive slow...steer fast

 

[SkipVought]

Sorry, I have no good answer.

Skip,

_[red][/red]