Copy Template With Updated Values

[BeanCounters]

Hi All,

I could really use your help. I need to create a copy of a template, based on data from another worksheet in the workbook. The catch is: Each newly created copy of the "Template" must be able to retain the date from the entry made in the "Start Here" worksheet.

I have a workbook with three main sheets. Two of them are hidden:

• Start Here
• Template - hidden
• Clients - hidden

The goal is to have an employee enter a date in the "Start Here" sheet. Once the employee enters the date, they click a button to "Create New Worksheet" based on the hidden sheet "Template." A new "Template2" worksheet opens with the previously entered date from "Start Here" copied and pasted into a cell in the newly created "Template2" sheet.

My code works fine.......until I try to use the "Start Here" sheet again and create another new sheet with a different date. I can get a new "Template3" sheet, but the new date entered in the "Start Here" field overrides the date entered in the previously created "Template2" sheet. I need each new copy to retain it's individually entered date.

Here is my code:

Sub AddNewSheet()
Worksheets("TEMPLATE").Copy Before:=Worksheets(2)
Worksheets(2).Visible = xlSheetVisible

End Sub

I have also tried this code (works the same as the above code)

Sub CopyTSheet()
'Replace "Template" with the name of the sheet to be copied.
ActiveWorkbook.Sheets("Template").Copy _
After:=ActiveWorkbook.Sheets("Start Here")
ActiveWorkbook.Sheets(2).Visible = xlSheetVisible
End Sub

What am I missing? Can you tell I'm new at this?

I have two additional questions, but would love to solve the above problem first.

Question #2: How do I get the newly created "Template" sheet to be the active sheet when created.
Question #3: How can I rename the newly created sheet based on the content of a cell instead of it naming it "Template3" "Template4" "Template5" etc. Goal is to have it look at the date entered and use it as the sheet name. Ex: Date entered in the "Start Here" sheet is 01/07/2010 and the new sheet name when created will read "Jan 07 2010."

Many thanks to anyone that can give me a hand.

Sincerely,

Julie

 

[Trevor G]

Welcome to the Forum Julie

Try this out.

Sub copyTemplateSheet()
Dim myShtName
myShtName = Month(Sheets("Start here").Range("A2")) _
& "-" & Day(Sheets("Start here").Range("A2")) _
& "-" & Year(Sheets("Start here").Range("A2"))

Sheets("Template").Copy before:=Sheets(2)
ActiveSheet.Name = myShtName
End Sub

 

[BeanCounters]

Trevor-Thanks for the quick reply.

I tried your code, but I get a Run-time error '9': subscript is out of range.

Not sure what I'm doing wrong.

 

[Trevor G]

I have tested this several times and it works fine.

Where does the script stop?

 

[BeanCounters]

Ok, fixed the compile error. Sorry about that, it was the way I had named the start here sheet.

I tried your code and it does rename the worksheet, but it overwrites the previously created copy and creates a new copy called "Template2" and hides it. I need it to create a new worksheet with the date as the name and keep only the original "Template" sheet hidden.

I can't thank you enough for helping me with this.

Julie

 

[BeanCounters]

Trevor,

Maybe it will help if I share the workbook. http://db.tt/dhGKhY2

 

[Trevor G]

Julie you have your code behind the worksheets, sorry to say this isn't the place to do this.

I would suggest that you cut the code from the worksheet, place this code into a Module Sheet (Insert Menu and Module) this will then work fine. You will then need to adjust the button on the sheet to select the sub routine.

Sub copyTemplateSheet()
Dim myShtName
Application.ScreenUpdating = False
myShtName = Month(Sheets("Start here").Range("I14")) _
& "-" & Day(Sheets("Start here").Range("I14")) _
& "-" & Year(Sheets("Start here").Range("I14"))
Sheets("Template").Visible = True
Sheets("Template").Copy before:=Sheets(2)
ActiveSheet.Name = myShtName
Sheets("Template").Visible = False
Application.ScreenUpdating = True
End Sub

You also have code for Autpen behind a worksheet this should go into the Workbook Events. Double click in the VBA screen where it states ThisWorkBook and select the first drop down that says Object and change to Workbook.

Your code for the workbook on open should go here.

Private Sub Workbook_Open()
' This macro will put today's date as the default new tab name
Sheets("Start Here").Activate
'Sheets("Menu").Select I changed the sheet name based on the sample you uploaded
Range("D5").Select
Selection.Formula = "=text(now(),""mmm dd yyyy"")"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Selection.Columns.AutoFit
Range("D8").Value = ""

End Sub

 

[BeanCounters]

Trevor,

You are AWESOME! Thank you so very much--it works perfectly!

Really Sincerely,

Julie

 

[Trevor G]

Please to read you have working solution Julie and glad to help.