insert, name and format new worksheet

[adjgiulio]

Hi,

I've an Excel Spreadsheet (Report) with n worksheets.
I'd like to create a VBA macro that, when I open the Spreadsheed:
a) asks if I want to add a new worksheet. If the answer is yes, then:
b) prompts a form to input the worksheet name
c) creates a new worksheet using the name inputed and positions the
worksheet as last
d) assigns a template (T_Z) to the worksheet

Alternatively steps c) and d) might be as well:

c) copies an existing worksheet Template_Z, renames it using the name
inputed and moves it to last place

Is this doable?

Thanks a lot!

G

 

[Rick Rothstein \(MVP - VB\)]

I've not worked (code-wise) with Templates before, so I am not sure how to
implement your first option; however, I think the code below will address
your second option, assuming we are talking about a straight copy of a sheet
named Template_Z...

Private Sub Workbook_Open()
Dim Answer As Variant
Dim Answer2 As String
Dim WS As Worksheet
Answer = MsgBox("Do you want to add a new sheet?", _
vbQuestion Or vbYesNo, "New Sheet Question")
If Answer = vbYes Then
Answer = Trim(InputBox("What is new sheet's name?", "Get New Name"))
If Len(Answer) = 0 Then Exit Sub
For Each WS In Worksheets
If UCase(WS.Name) = UCase$(Answer) Then
Do
Answer2 = Trim(InputBox("Sorry, that name already exists, " & _
"chose another name.", "Duplicate Sheet Name"))
If Len(Answer2) = 0 Then Exit Sub
If UCase(Answer) <> Trim(UCase(Answer2)) Then
Answer = Answer2
Exit For
End If
Loop
End If
Next
Worksheets("Template_Z").Copy After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = Answer
End If
End Sub

Rick

 

[adjgiulio]

however, I think the code below will address

Thanks!

G