+ Reply to Thread
Results 1 to 5 of 5

Best Technique to clone worksheet

  1. #1
    Registered User
    Join Date
    06-29-2005
    Location
    England
    Posts
    50

    Best Technique to clone worksheet

    I have an application that adds worksheets as required by the user. This includes various graphics objects etc using the following code:

    Please Login or Register  to view this content.
    This works fine, however, it does not copy the macros from the source sheet such as :

    Please Login or Register  to view this content.
    As you can see, the macros themselves are just calls to the actual routines in a VBA module.

    Also what needs to be copied are some custom properties such as:

    Please Login or Register  to view this content.
    What is the best way of achieving a complete clone of the worksheet including all macros etc?

    Regards

    Rich

  2. #2
    DM Unseen
    Guest

    Re: Best Technique to clone worksheet

    Rich,

    VBA code is copied over using the VBIDE library which gives access to
    all VBA project properties and modules and their contents. This however
    also triggers security warnings when you change this through code
    (viral behavior), and the VBIDE library might also be not installed
    (not standard).

    Why not using workbook events or class modules to generalise sheet
    events?

    DM Unseen


  3. #3
    Registered User
    Join Date
    06-29-2005
    Location
    England
    Posts
    50
    Cheers DM. I'll have a look into classes. (I'm not a novice programmer, but I'm a relative novice to Excel so expect some more questions!)

    Regards

    Rich

  4. #4
    Tushar Mehta
    Guest

    Re: Best Technique to clone worksheet

    You need to use a class only if you use application level events. Of
    course, it is a very good idea to do so. Start with XL VBA help for
    'Using Events with the Application Object'

    It is also a very good idea to separate the data from the code. I, for
    one, don't distribute code in workbooks that will also contain data.

    --
    Regards,

    Tushar Mehta
    www.tushar-mehta.com
    Excel, PowerPoint, and VBA add-ins, tutorials
    Custom MS Office productivity solutions

    In article <[email protected]>,
    [email protected] says...
    >
    > Cheers DM. I'll have a look into classes. (I'm not a novice programmer,
    > but I'm a relative novice to Excel so expect some more questions!)
    >
    > Regards
    >
    > Rich
    >
    >
    > --
    > Rich_z
    > ------------------------------------------------------------------------
    > Rich_z's Profile: http://www.excelforum.com/member.php...o&userid=24737
    > View this thread: http://www.excelforum.com/showthread...hreadid=384850
    >
    >


  5. #5
    DM Unseen
    Guest

    Re: Best Technique to clone worksheet

    Tushar, Rich

    Yes, application level events are the most powerful, and you *need* a
    class module to get those.(you should study them though, understanding
    them is vital to using events in Excel).

    But sometimes you do not want those powerful events, just the same
    events for a couple of selected sheets (remember unescesarely running
    VBA events clears the XL UNDO stack, so do not run an application level
    event to catch just one specific things within a few sheets, your UNDO
    stack will be constantly cleared!). Then a class module that is
    instantiated for just a few sheet wil catch just the events you want,
    while not duplicating any code in any worksheet.

    DM Unseen


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1