+ Reply to Thread
Results 1 to 4 of 4

How to copy code from one sheet to another

  1. #1
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    How to copy code from one sheet to another

    My workbook contains a large sheet (240 columns) which is generated each year anew from a template sheet. The user has to be able to see a userform and this is made possible by clicking anywhere in row(2). There is code in the sheet module fired by Selection_Change event to do this
    I am trying to transfer this code from the Template sheet to the worksheet as it is generated, and have found some code that I have modified to do this.
    Please Login or Register  to view this content.
    This fails with a "Substrate out of range " error at line starts Set Codecopy=.
    Sheet "Template" does exist and has code in Worksheet_SelectionChange event. I expect that the next line might well come up with an error message too.
    I have ticked the MSVB extension in references.
    I have gone wrong somewhere and would welcome some help.
    John

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,397

    Re: How to copy code from one sheet to another

    If the macro you want to copy is a Selection_Change event, then you can place the macro in the code module for ThisWorkbook instead of the worksheet code module and it will apply to all sheets in the workbook. This macro will apply to all sheets except the "Template" sheet. This way you don't have to have a copy in each worksheet code module.

    Please Login or Register  to view this content.
    If you want the code to apply to all sheets, then this would do it:

    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: How to copy code from one sheet to another

    Mumps1 many thanks for your post which I am pretty sure is the answer.
    The workbook I am updating gives our management accounts each month. Each month has 20 columns hence the need for a worksheet 240 columns long. It is only these sheets that need the facility given by my macro. They are all called ManagementAccounts yyyy-yyyy and so I can easily see a way of modifying your snippet to include all these sheets and thus improve the old sheets as well as new ones, Marvellous!!
    Thanks
    John

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,397

    Re: How to copy code from one sheet to another

    Try:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Copy template sheet to a new sheet with the code in the teimplate sheet
    By BRISBANEBOB in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-17-2018, 10:41 PM
  2. [SOLVED] issue with number formating in actual vba copy code from 1 sheet to another sheet
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-11-2017, 10:35 AM
  3. VBA code - copy info from sheet 1 to sheet 2 but NOT COPY BLANK CELLS
    By Yossarian1980 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-14-2017, 06:09 PM
  4. code to copy first rows and sheet titles from separate csv files into summary sheet
    By freshmint in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-08-2017, 08:38 AM
  5. [SOLVED] Vba code to copy unique email id data from input sheet to output sheet
    By UPA in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-09-2017, 07:19 AM
  6. Replies: 2
    Last Post: 12-17-2013, 11:51 AM
  7. Replies: 5
    Last Post: 12-12-2013, 02:53 PM

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