Projects Possible

When using Excel I find that I will often times want to setup a worksheet that I can use as a template.  For example I might have some monthly sales or inventory tracking that I do and I want to use the same format each month,  but I want each month to be on a separate worksheet all within the same workbook.   I usually do this by creating the worksheet the way that I want it and then I just right click on the worksheet tab and change the name to something like ”SalesTemplate” or “InventoryTemplate” or ”PayrollTemplate”, whatever the data is.

Now when I need to start a new period of time or a new project I simply Add the worksheet(s) to my Excel workbook, go to the template worksheet, copy the entire thing and then paste the copy into the worksheet that I just added…. Very simple process. 

If you do this periodically and just with one worksheet it isn’t too bad to do it manually but if you find yourself needing to copy more than one and you are doing it regularly here is the snippet of a script that might give you some ideas of how you may automate this process.   This script is attached to a button called “Add Project” in my workbook and what it does is:

1. Prompts me for the next Sheet number to assign
2. Does a little bit of error checking
3. Copies my “Sheet Template” to a new worksheet and places it BEFORE Sheet99 in my Workbook
4. Changes the name of the worksheet to “Sheet##” using the number I entered above for ##
5. Places the number I entered in step 1 into the C6 cell
6. And then it protects the worksheet.
7. It then repeats the process using my “Info Template.

Sub NewWorksheets()
  Dim ans As String
  ans = InputBox(”What is the next sheet #?”, “New Sheet Number”, “”)

If ans = “” Then Exit Sub
  On Error Resume Next
  If Worksheets(”Sheet” & ans) Is Nothing Then
  ActiveSheet.Name = “Selector”
  ActiveSheet.Range(”D36″).Value = ans
  Sheets(”Sheet Template”).Copy Before:=Sheets(”Sheet99″)
  ActiveSheet.Name = “Sheet” & ans
  ActiveSheet.Range(”C6″).Value = ans
  ActiveSheet.Protect
  Sheets(”Info Template”).Copy Before:=Sheets(”Sheet99″)
  ActiveSheet.Name = “Info” & ans
  ActiveSheet.Range(”I3″).Value = ans
  ActiveSheet.Protect
 Else
  MsgBox “A sheet with that name already exists. Sheet not added.”
  Exit Sub
 End If
  On Error GoTo 0
End Sub

A very quick and painless process and I know that it is correct every time and that I have the values placed into the fields that I need them in.  Since I use this to keep track of individual project information I could be adding projects at any time and this script really streamlined the process for me.  You can also learn how to use HYPERLINKING to navigate through a workbook like this on my other post located HERE.

This post was read 67 times until now