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

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 befo=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

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

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

"Neale" wrote:

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 befo=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