+ Reply to Thread
Results 1 to 4 of 4

How to create worksheet template - NOT workbook template

  1. #1
    Registered User
    Join Date
    01-30-2012
    Location
    cairns
    MS-Off Ver
    Excel 2003
    Posts
    29

    How to create worksheet template - NOT workbook template

    Hi,

    I have a workbook containing multiple worksheets attached. At the end of each month I want to create a new worksheet using a template so that formulas etc are transferred to a new sheet but all other other sheets remain the same, the idea being that I can add 'June', 'July' etc as time goes by. Saving as a .xlt in the templates folder is not what I'm after as this opens up a whole workbook, overwriting the other tabs. Ideally I want to right click a tab at the bottom, select 'Insert' and open up a template, rename it for the appropriate month, without this effecting the other tabs in any way.

    How can I do this?

    Thanks,

    Will
    Attached Files Attached Files

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: How to create worksheet template - NOT workbook template

    Create a copy sheet & hide it in the workbook.

    The summary sheet example demonstrates how to do this, with code to generate a new sheet from it if required
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    01-30-2012
    Location
    cairns
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: How to create worksheet template - NOT workbook template

    Hi again RoyUK,

    I am not understanding how to get that to work in my workbook, apologies but you're talking to someone who isn't savvy at all with codes. How do I get their summary sheet code below to work and become relevant for my workbook?

    Please Login or Register  to view this content.
    i.e.
    Where and how do I save my template? (I don't need a mssg box to appear either), is it saved into the normal template folder or somewhere else?
    How do I insert the new sheet once it's saved?

    - Will

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: How to create worksheet template - NOT workbook template

    If you only have 12 sheets required why not create the 12 sheets and unhide each one when required?

    In your example I have created a template sheet based on May 2010. This is called Template & is made very hidden,

    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    You will see on the left the workbooks objects - WorkBook, Sheet1 etc
    Select the template sheet & in it's properties listed below find Visible & change to VeryHidden

    If you look at the example you will see code in the WorkBook module. This is set to run when the workbook opens with macros enabled. It checks whether a sheet alread exists for the current month if it doesn't then you are asked if you want to create one. If you choose yes then the template sheet is copied to the end of the workbook & renamed as current month.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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