+ Reply to Thread
Results 1 to 6 of 6

Copying a template sheet's content to a series of newly created sheet tabs.

  1. #1
    Registered User
    Join Date
    10-30-2015
    Location
    America
    MS-Off Ver
    365
    Posts
    3

    Copying a template sheet's content to a series of newly created sheet tabs.

    I would like to copy a template sheet's contents(basically the entire sheet) to each newly created tab. The code below creates each new sheet tab and takes the values of a column range of companies on a separate sheet in the same workbook to name each new sheet tab. Where I am running into an issue is trying to get the actual "Template sheet"(within the same workbook) and it's contents copied to each one of these newly created tabs. This is the code I have so far.

    Please Login or Register  to view this content.
    I would appreciate any suggestions that would copy that "Template Sheet" to each of the new tabs as the tab itself is being created.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Copying a template sheet's content to a series of newly created sheet tabs.

    Hi,

    I think I'd simplify this to the following. First create a range name "SheetNames" that covers your list of sheet names

    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    10-30-2015
    Location
    America
    MS-Off Ver
    365
    Posts
    3

    Re: Copying a template sheet's content to a series of newly created sheet tabs.

    Thanks for the quick response Richard. I seem to be getting an error though. not sure why. I am selecting/highlighting the cells in the column that I would like named tabs to be created for but when I click the button I mapped this macro to, the error appears.

    I have included the example sheet with this reply so you can have a better idea of how the data is formatted. I appreciate your help.
    Attached Files Attached Files

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Copying a template sheet's content to a series of newly created sheet tabs.

    Hi,

    You haven't named the I2:I6 range of cells on the Summary sheet as "SheetNames" which is what the macro is expecting.
    Neither have you changed the template sheet name in the macro. Your tab name is "Revenue Template". Your original hadn't actually said what it was called so I assumed it was just called "Template"

    However now I've seen your workbook I know the VBA code name for the "Revenue Template" sheet is Sheet2. Therefore it's much better to use the VB code name because if you ever change the tab name and attempt to run the macro you'd get an error. Hence use

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    10-30-2015
    Location
    America
    MS-Off Ver
    365
    Posts
    3

    Re: Copying a template sheet's content to a series of newly created sheet tabs.

    Hey Richard,

    Thanks again, but I am still getting the same error. I have attached an updated excel sheet that has your updated code included. Any ideas? Thanks again.
    Attached Files Attached Files

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Copying a template sheet's content to a series of newly created sheet tabs.

    Hi,

    You still haven't created the range name "SheetNames". See my first comment in my last post.
    Currently this should cover I2:I6 on the Summary.

    If you periodically add new rows to the summary then it would be advisable to make this range a dynamic range name so that it automatically adjusts. If so then define it thus:

    Formula: copy to clipboard
    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. hyperlink of newly created sheet
    By colin.dickson in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-17-2015, 08:08 AM
  2. [SOLVED] Worksheet Activate when user selects sheet NOT when sheet is created from a template sheet
    By y0rk1e72 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-26-2012, 02:47 PM
  3. [SOLVED] Pulling data from a main sheet and placing it onto a newly created sheet
    By cbomination in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 05-25-2012, 10:12 AM
  4. Getting Macro to reference newly created sheet
    By creuther in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-27-2011, 01:04 AM
  5. Updating Summary Sheet from Newly Created Sheet
    By Applem21 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-07-2010, 01:16 PM
  6. How to copy to newly created sheet and save as PDF?
    By joanneb in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-30-2009, 04:05 PM
  7. Replies: 0
    Last Post: 05-29-2009, 09:42 AM

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