PDA

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



ahmed_k
07-02-2009, 07:28 PM
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
07-02-2009, 08:36 PM
Hi ak,

This may give you an idea.

ahmed_k
07-02-2009, 10:12 PM
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
07-03-2009, 12:52 AM
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
07-03-2009, 04:38 AM
Thanks rbrhodes.
works like a charm
:friends: