Consulting

Results 1 to 5 of 5

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

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    Jul 2009
    Posts
    8
    Location

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

    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.

    Help will be greatly appreciated.

    Thanks

  2. #2
    VBAX Expert
    Joined
    Feb 2005
    Location
    Nanaimo, British Columbia, Cananda
    Posts
    568
    Location
    Hi ak,

    This may give you an idea.
    Cheers,

    dr

    "Questions, help and advice for free, small projects by donation. large projects by quote"

    http:\\www.ExcelVBA.joellerabu.com

  3. #3
    VBAX Regular
    Joined
    Jul 2009
    Posts
    8
    Location
    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"

  4. #4
    VBAX Expert
    Joined
    Feb 2005
    Location
    Nanaimo, British Columbia, Cananda
    Posts
    568
    Location
    Hi Ahmed,

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

    I trust you can fix it?

    [VBA]
    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

    [/VBA]
    Cheers,

    dr

    "Questions, help and advice for free, small projects by donation. large projects by quote"

    http:\\www.ExcelVBA.joellerabu.com

  5. #5
    VBAX Regular
    Joined
    Jul 2009
    Posts
    8
    Location
    Thanks rbrhodes.
    works like a charm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •