Assign a macro Name to a button based off cell reference

[G_Sereez]

I have a workbook that copies and moves on sheet to a new workbook and saves it to a selected destination. I have saved code into that sheet so that it carries over to the new workbook. I need to code so that It will assign the macro name to a shape in the workbook.


So the code before editing looks like this:
Selection.OnAction = "Sheet17.PEER_REVIEW_COMPLETED"
The issue is that if someone adds a sheet to the workbook that moved sheet is no longer sheet 17. So, I created a formula in a cell that spits out "Sheet17.PEER_REVIEW_COMPLETED" but the 17 automatically changes based on how many sheets there are. How do I assign the Selection.OnAction to reference that cell?

 

[Bill Rockenbach]

It appears that "Sheet17" is the code name of the sheet and should not change when other sheets are added. What is the formula you use to "spit" out the reference?

 

[Bill Rockenbach]

Maybe you can use a Defined Name See Preventing Problems With Worksheet Renaming