Solved: Create copy of worksheet plus code in the same workbook

[ahmed_k]

Hi,
I have a master worksheet with dropdowns and buttons from the forms menu which have macros assigned to them. I want users to be able to make copies of the master in the same workbook. I am able to create copies of master with the code. The problem is when I create a new copy, the macros attached to the dropdowns and buttons don't update to the code on the current sheet. The dropdowns and buttons on the new sheet call onto the macros in the master sheet. Therefore if I press a button on the new sheet it makes a change in the master sheet and all the other copies as well. :rofl:

Help will be greatly appreciated.

Thanks

[rbrhodes]

Hi ak,

This may give you an idea.

[ahmed_k]

Thanks for your help rbrhodes.

Your code attaches the macro from the new sheet to the button but it also puts the workbook name as follows "Book1.xls!Sheets2.button2". This gives the following error "The macro 'Book1.xls!Sheets2.button2' cannot be found"

[rbrhodes]

Hi Ahmed,

Sorry about that the lines should read "Sheet" not "Sheets"!!

I trust you can fix it?


Option Explicit
'Copies sheet to end of sheets
Sub makecopy()

'Copies current sheet to end of Sheets count
'Changes buttons macros to current sheet

Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)

'This line should be "Sheet" singular, not "Sheets" plural
ActiveSheet.Shapes("Button 1").OnAction = "Sheet" & ActiveSheet.Index & ".button1"

'This one too
ActiveSheet.Shapes("Button 2").OnAction = "Sheet" & ActiveSheet.Index & ".button2"
End Sub

[ahmed_k]

Thanks rbrhodes.
works like a charm
:friends: