Auto Update Macro when duplicating worksheet

[paulm88]

I have a macro on Sheet1(Sheet1.OpenCreative) assigned to an object that is referencing a cell with a dynamic hyperlink. When I duplicate this sheet, i would like the macro in sheet 2 to automatically change to Sheet2.OpenCreative. Is this possible? Macro code below:

Sub OpenCreative()
URL = Range("A62").Text
Shell "C:\Program Files (x86)\Adobe\Acrobat DC\Acrobat\Acrobat.exe " & URL, vbNormalNoFocus
End Sub

 

[Yongle]

1. BOTH procedures below are in Sheet1 's sheet module

Sub RunOpenCreative()
    Call OpenCreative
End Sub

Sub OpenCreative()
    URL = Range("A62").Text
    Shell "C:\Program Files (x86)\Adobe\Acrobat DC\Acrobat\Acrobat.exe " & URL, vbNormalNoFocus
End Sub

2. Replicate Sheet1 (right click on sheet tab \ Move or COPY ..etc)

3. BOTH procedures are also in replicated sheet and RunOpenCreative in the NEW sheet refers to OpenCreative in the NEW sheet

Is that what you are wanting to do?

 

[paulm88]

Thanks for your response. Unfortunately, this doesn't seem to be working for me, as the object in sheet2 still opens the location of the URL in sheet1. Therefore, I have to manually assign the new macro to the sheet2 object, which is what i'm trying to avoid.

1. BOTH procedures below are in Sheet1 's sheet module

Sub RunOpenCreative()
    Call OpenCreative
End Sub

Sub OpenCreative()
    URL = Range("A62").Text
    Shell "C:\Program Files (x86)\Adobe\Acrobat DC\Acrobat\Acrobat.exe " & URL, vbNormalNoFocus
End Sub

2. Replicate Sheet1 (right click on sheet tab \ Move or COPY ..etc)

3. BOTH procedures are also in replicated sheet and RunOpenCreative in the NEW sheet refers to OpenCreative in the NEW sheet

Is that what you are wanting to do?

 

[Yongle]

It would help if you told us what the "object" is.
Different objects behave differently.
By default, an active-x command button sitting on sheet1 which is "replicated" would do what you want on sheet2 because the code sits in the sheet module.
Which object are you using?

 

[paulm88]

Apologies Yongle, it turned out the object was an image rather than an Active X control...

Resolved this now!

Thanks for your help.

It would help if you told us what the "object" is.
Different objects behave differently.
By default, an active-x command button sitting on sheet1 which is "replicated" would do what you want on sheet2 because the code sits in the sheet module.
Which object are you using?

 

[Yongle]

Thanks for the feedback