Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Mar 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cloning Macros (97 sr2)

    I am developing some event driven macros (such as worksheet_activate) but the workbook has 60+ worksheets. I was wondering if there was any way to "clone" a macro onto multiple worksheets AFTER the worksheets have been created?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cloning Macros (97 sr2)

    Two suggestions:

    1- You can copy the macro and then paste it into each worksheet event routine. However, this could be a major problem if you need to change the macro at some time in the future.

    2- Put all of the macro code into a Sub procedure or Function in a general module. Then call the Sub or Function from each of the event routines.
    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Cloning Macros (97 sr2)

    If you want the exact same code to run for all sheets when they are activated, try the
    Workbook_SheetActivate(ByVal Sh As Object).

    In the project explorer, double click on the ThisWorkbook object, and place the code there. Ther are other events there that can also be coded and will then effect all sheets. If you need to alter the code slightly for some sheets, use the argument value, (e.g If Sh.Name = "Sheet1" Then ...etc.)

    Andrew C

Posting Permissions

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