Copying a formatted "template" sheet and naming new sheet via user form

[Neale]

Hi. I've done some hunting around and reading and came up with the
following code that creates a new blank sheet and names it via a user
form, but I can't work out how to do the same thing if I want to use a
formatted template worksheet. Please help!

Sample code I've come up with so far:

'Create a new blank worksheet
'Name the worksheet by entering the employee name and number
'in the appropriate boxes
'Clicking OK will transfer the name to the worksheet tab
Private Sub cbNewEmpSheetNameFormOK_Click()
Dim NewSheet As Worksheet
On Error Resume Next
Set NewSheet = Worksheets(tbxNewEmpSheetNameFormName.Text & "-" _
& tbxNewEmpSheetNameFormNo.Text)
If Err > 0 Or NewSheet Is Nothing Then
Worksheets.Add before:=Worksheets(Worksheets.Count)
ActiveSheet.Name = tbxNewEmpSheetNameFormName.Text & "-" _
& tbxNewEmpSheetNameFormNo.Text
Worksheets(2).Select
'The sheet is checked to ensure that it's not a duplicate of an
already existing sheet
Else
Beep
MsgBox "Sheet already exists!"
End If
Unload Me
End Sub

 

[Guest]

hi,
understand. VB is not psychic. you have to tell it everything.
in the code you posted, VB is getting the naming criterial from 2 text boxes
on the form. the information got into the text box somehow....how?.....??
so if you are not using a form, you can put the information in cells. say A1
and A2'
replace
ActiveSheet.Name = tbxNewEmpSheetNameFormName.Text & "-" _
& tbxNewEmpSheetNameFormNo.Text
with
Dim one As Range
Dim two As Range
Set one = Sheet1.Range("A1")
Set two = Sheet1.Range("A2")
Sheets.Add
ActiveSheet.Name = one.Value & "-" & two.Value

worked in xl3k
regards
FSt1