Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Oct 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert sheet based on Template and Cell Content

    Good Day,

    I am hoping this is even possible in Excel. I have a good foundation in using formulas but VBA and higher functions yet escape me.

    We are creating a work management document and want to have a listing of projects. When a new project is entered into column B is it possible to have excel insert a new sheet in the workbook, based on a template and named for the new project.

    Projects will always been entered in the next available row and will not be deleted even when complete. I have attached the Work Management Document and the template we would like to use for subsequent pages.

    Thanks in advance for any assistance provided.

    Curi
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Curi,

    Yes this is possible. However, you can't do it with the template workbook you have because it contains 2 sheets! (one is hidden "calcs"). This process works just fine and can be automated with VBA as you want but you have to get rid of the Calcs sheet and then save the workbook as a .xlts (template) file.

    That said there is another way to accomplish this which would be to open the template workbook and then copy the two sheets into the base workbook. The problems with this is you would have to create some kind of naming convention to rename the Calcs sheet as you can only have one and if you are going to have many projects in this workbook you can see the problem.

    In this case I think you would be better served to move all of the calculations on the Calcs sheet to the Project Timeline sheet some where to the right out of the way or just hide the rows. This way all your references with be internal to the sheet and not cause any problems when adding the sheet from a template as in the first scenario.

    Please advise as to which way you wish to proceed. HTH
    Last edited by RetiredGeek; 2013-10-30 at 08:47.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Curi

    ..what RG meant to say first was "welcome to the lounge.."
    Other than that, RG always offers good advice.

    zeddy
    Last edited by zeddy; 2013-10-30 at 12:24.

  4. #4
    New Lounger
    Join Date
    Oct 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Retired Geek,

    Thank you for you reply! The first scenario you have outlined is exactly what I am looking to do. I have revised the template to just being on a single sheet with the calculations off onto the right hand side. I have attached the revised template here. I do realise this sheet needs to be saved in template format but am at a loss as to how to attach a template to my post -- It tells me its an invalid file format.

    Thank again for your assistance!

    Curi
    Attached Files Attached Files

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Curi,

    Ok, here's some code that does what I think you're after. It will take 2 modules (although it could be combined into one if you wish.
    Module #1 goes into the Summary Sheet Object:
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    '*** Limiting the Worksheet_Change event to a firing when a cell
    '*** in Col A is changed
    
       Dim isect As Range
       
       Set isect = Application.Intersect(Range("A:A"), Target)
       If isect Is Nothing Then
    '     MsgBox "Ranges do not intersect"
       Else
         '***Prevent following code from refiring Change Event ***
         Application.EnableEvents = False
    '     MsgBox "A1 Changed", vbOKOnly + vbInformation, "Cell Changed"
    
    '***** I'm calling the code but you could insert it here if you wish! *****
         CreateNewProject  'Call code to insert & rename template
         Application.EnableEvents = True '*** Reset Events ***
       End If
    
    End Sub   'Worksheet_Change()
    The second module goes into a General Module:
    Code:
    Option Explicit
    
    Sub CreateNewProject()
    
       Dim shtNewSheet   As Worksheet
       Dim shtCurSheet   As Worksheet
       Dim lFound        As Long
       Dim zTemplatePath As String
       Dim zNewSheetName As String
       
       Application.ScreenUpdating = False
       Set shtCurSheet = ActiveSheet
       zNewSheetName = ActiveCell.Value & "-Project Timeline"
       
       On Error Resume Next
       Sheets(zNewSheetName).Activate
       lFound = Err
       shtCurSheet.Activate
       On Error GoTo 0
       
       If lFound = 0 Then
         MsgBox "A project sheet for: " & zNewSheetName & _
                " already exists." & vbCrLf & vbCrLf & _
                "No Action Taken!", vbOKOnly + vbInformation, _
                "Project Sheet Already Created"
       Else
    '*** Change Path Below to match your Template location ***
       zTemplatePath = "C:\Users\Bruce\AppData\Roaming\Microsoft\Templates\"
    
       Set shtNewSheet = Sheets.Add(Type:= _
                       zTemplatePath & "Project Timeline.xltx", _
                       After:=Worksheets(Worksheets.Count))
    
    '*** You can adjust the naming convention below as desired ***
       shtNewSheet.Name = zNewSheetName
    
       shtCurSheet.Activate
    
       End If
        
    End Sub   'CreateNewProject
    Notes:
    Don't forget to save your template as a .xltx file in your template folder.
    Change the variable zTemplatePath to your template folder location.
    The code checks to make sure you don't create 2 projects for the same name.
    Read the comments in the code for changes you need/can make.

    HTH

    Workplan.xlsm
    Last edited by RetiredGeek; 2013-10-30 at 16:31.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Here is a different approach also using the Worksheet_Change event subroutine. When a new project is entered in column B, a copy of the hidden template will be created and the sheet name changed to match the project name.

    HTH,
    Maud

    WorkPlan1.png

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Errorhandler
    Application.ScreenUpdating = False
    '---------------------------------------------
    'TEST NUMBER OF SELECTED CELLS IS ONLY ONE
    'TEST RANGE TO BE IN COLUMN B BELOW ROW 2
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, ActiveSheet.Columns(2)) Is Nothing And Target.Row > 2 Then
        If Target.Value = "" Then GoTo Errorhandler
        project = Target.Value 'GET VALUE FROM SHEET
        Sheets("Project Timeline").Copy after:=Sheets(1) 'COPY HIDDEN SHEET
        Worksheets(2).Visible = True  'MAKE NEW SHEET VISIBLE
        Worksheets(2).Select
        ActiveSheet.Name = project  'RENAME SHEET TO CELL VALUE
        Worksheets("Summary").Activate
    End If
    Errorhandler:
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    Feb 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Maud

    Your code is exactly what I'd like to do, but when I enter the value in my cell, it changes the name of the active sheet instead of creating a new sheet. I have the template sheet hidden as you suggested. My cell is in column A below row 18, which I modified your code to allow. I also renamed your sheet references to my sheet names.

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Maud

    ..I like your method. It doesn't need an external template, and keeps everything needed in the same file.
    I made a minor modification to check that the new project name entered hasn't already been used i.e. no duplicate project names allowed. (RG very nicely included a test for a project sheet that was already there!)

    see attached file.

    TMB: Welcome to the Lounge!
    If you could let us see your code, we could guide you on where the problem is, and how to fix it.

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2014-02-24 at 06:54.

  9. #9
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Excuse me for butting in but ...

    I understand all of this apart from - What actually calls "worksheet_change"? How do we get into that code in the first place?

    cheers

    Alan

  10. #10
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Plus - if you go back to cell B4 and change its name (and this is the same even if you have data in cells below), it will add a new tab of the new name but not get rid of the old one.

    Just thought you might want to know this

    Alan

  11. #11
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by alan sh View Post
    Excuse me for butting in but ...

    I understand all of this apart from - What actually calls "worksheet_change"? How do we get into that code in the first place?

    cheers

    Alan
    OK - done some looking up and it seems if you write a "worksheet_change" routine, it gets called everytime a cell gets changed. Neat. So, next question - where are all these built in routines (that we need to write) documented? I assume there are loads of them.

    Cheers

    Alan

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Alan,

    If you open the sheet's module, in the left dropdown box at the top select worksheet. On the right dropdown, all the available empty event subroutines will be listed. Entering code into that event subroutine will be carried out when the event is triggered.

    HTH,
    Maud

  13. The Following User Says Thank You to Maudibe For This Useful Post:

    alan sh (2014-02-25)

  14. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Zeddy,
    Thanks for the tweak

  15. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Alan

    Re: your post#10: You are correct, if you overtype an existing cell, the associated project sheet doesn't get deleted. This is intentional, as the original post from Curi says "and will not be deleted even when complete".
    (If we wanted to, we could update the vba to prevent the overtyping and changing of an existing entry).

    Re your post#9: Cell contents can be changed by macros (vba) or more commonly by the User, for example when you type something into a single cell, when you delete the contents of a single cell, or when you change the contents of lots of cells e.g. by first copying some cells (using Ctrl-C) and then 'pasting' to a location (which means lots of cells have been changed in 'one go'). If you copy a single cell and then immediately paste it back with Ctrl-V, Excel still considers you have 'changed' the cell. If a cell has a formula in it, and the formula result changes because of a change somewhere else, Excel does not consider the updated formula as a 'change event'.

    The change event allows for some very nifty automatic routines to be executed when certain defined conditions have been met.
    You can monitor specific cells for specific changes and make decisions on what routines to run.
    It is one of the most powerful tools available in Excel and is well worth investigating.

    zeddy

  16. #15
    New Lounger
    Join Date
    Feb 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Zeddy

    I just got the code to work. I had the actual worksheet #'s wrong. I have another couple of questions if someone could answer them.

    Can it be limited to a specific range of rows within column A? The way Maud has it written, (which is great by the way) it will continue down the column. I only have 10 rows that I would like this to work in (Column A rows 19-28).

    Also, once executed, is it possible for columns B,D,&F rows 19-28 & column I rows 31-40 to return a value based on information entered into the worksheets created by Maud's code? So, basically, the above mentioned cells would be formatted to receive values from sheets that technically don't exist yet. Sorry, I'm new to VBA.

Page 1 of 2 12 LastLast

Posting Permissions

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