Duplicate worksheet creation
 

I have read through several posts on this subject, but can't get my situation
to work.
I am trying to write code so that an exact copy of "Sheet1" is made and a
message box appears asking the user to name the new worksheet, for example
the new name might be TimNPayPeriod1.

How can I accomplish this?

Is the code entered in a new module or as part of the user form code?

Thanks for any help!

Duplicate worksheet creation
 

Sub CopySheet()
Dim nSheet As Worksheet
Dim NameBox As String
NameBox = Application.InputBox("Please type a name for the new
worksheet", "Creating New Sheet", , , , , , 2)
If NameBox = "False" Then Exit Sub
Sheets("Sheet1").Copy Befo=Sheets(2)
Set nSheet = ActiveSheet
nSheet.Name = NameBox
End Sub
HTH

Die_Another_Day
TimN wrote:
I have read through several posts on this subject, but can't get my situation
to work.
I am trying to write code so that an exact copy of "Sheet1" is made and a
message box appears asking the user to name the new worksheet, for example
the new name might be TimNPayPeriod1.

How can I accomplish this?

Is the code entered in a new module or as part of the user form code?

Thanks for any help!

Duplicate worksheet creation
 

Thanks That worked!

I have a user form that asks for some key pieces of information to be
entered. Upon entry the data is placed in various cells and calculations
occur on that data.

Is there a way to make this procedure (the worksheet copy) occur when the
user clicks "OK" on the user form?

"Die_Another_Day" wrote:

Sub CopySheet()
Dim nSheet As Worksheet
Dim NameBox As String
NameBox = Application.InputBox("Please type a name for the new
worksheet", "Creating New Sheet", , , , , , 2)
If NameBox = "False" Then Exit Sub
Sheets("Sheet1").Copy Befo=Sheets(2)
Set nSheet = ActiveSheet
nSheet.Name = NameBox
End Sub
HTH

Die_Another_Day
TimN wrote:
I have read through several posts on this subject, but can't get my situation
to work.
I am trying to write code so that an exact copy of "Sheet1" is made and a
message box appears asking the user to name the new worksheet, for example
the new name might be TimNPayPeriod1.

How can I accomplish this?

Is the code entered in a new module or as part of the user form code?

Thanks for any help!

Duplicate worksheet creation
 

yes.
Double-Click your "OK" Button in the editor. This should take you to
the code for this button and should look like this:
Private Sub CommandButton1_Click()

End Sub

Add my code (without the "Sub CopySheet()" and the "End Sub") in
between the Sub End Sub for the commandbutton

HTH

Die_Another_Day
TimN wrote:
Thanks That worked!

I have a user form that asks for some key pieces of information to be
entered. Upon entry the data is placed in various cells and calculations
occur on that data.

Is there a way to make this procedure (the worksheet copy) occur when the
user clicks "OK" on the user form?

"Die_Another_Day" wrote:

Sub CopySheet()
Dim nSheet As Worksheet
Dim NameBox As String
NameBox = Application.InputBox("Please type a name for the new
worksheet", "Creating New Sheet", , , , , , 2)
If NameBox = "False" Then Exit Sub
Sheets("Sheet1").Copy Befo=Sheets(2)
Set nSheet = ActiveSheet
nSheet.Name = NameBox
End Sub
HTH

Die_Another_Day
TimN wrote:
I have read through several posts on this subject, but can't get my situation
to work.
I am trying to write code so that an exact copy of "Sheet1" is made and a
message box appears asking the user to name the new worksheet, for example
the new name might be TimNPayPeriod1.

How can I accomplish this?

Is the code entered in a new module or as part of the user form code?

Thanks for any help!

Duplicate worksheet creation
 

When I do that, I get a run time error 1004: Copy method of worksheet class
failed

What might be causing that?

"Die_Another_Day" wrote:

yes.
Double-Click your "OK" Button in the editor. This should take you to
the code for this button and should look like this:
Private Sub CommandButton1_Click()

End Sub

Add my code (without the "Sub CopySheet()" and the "End Sub") in
between the Sub End Sub for the commandbutton

HTH

Die_Another_Day
TimN wrote:
Thanks That worked!

I have a user form that asks for some key pieces of information to be
entered. Upon entry the data is placed in various cells and calculations
occur on that data.

Is there a way to make this procedure (the worksheet copy) occur when the
user clicks "OK" on the user form?

"Die_Another_Day" wrote:

Sub CopySheet()
Dim nSheet As Worksheet
Dim NameBox As String
NameBox = Application.InputBox("Please type a name for the new
worksheet", "Creating New Sheet", , , , , , 2)
If NameBox = "False" Then Exit Sub
Sheets("Sheet1").Copy Befo=Sheets(2)
Set nSheet = ActiveSheet
nSheet.Name = NameBox
End Sub
HTH

Die_Another_Day
TimN wrote:
I have read through several posts on this subject, but can't get my situation
to work.
I am trying to write code so that an exact copy of "Sheet1" is made and a
message box appears asking the user to name the new worksheet, for example
the new name might be TimNPayPeriod1.

How can I accomplish this?

Is the code entered in a new module or as part of the user form code?

Thanks for any help!

Duplicate worksheet creation
 

My bad....it works now.

Final question (I hope)

If the user closes the message box without entering a name but just presses
OK, an error message occurs: Run-time error '1004'
Method 'Name' of object_Worksheet failed

However a copy is created with a default name of "Sheet(2)". How can I
force an entry in the message box to prevent this error?

Thanks again for your assistance it has been great!

"TimN" wrote:

When I do that, I get a run time error 1004: Copy method of worksheet class
failed

What might be causing that?

"Die_Another_Day" wrote:

yes.
Double-Click your "OK" Button in the editor. This should take you to
the code for this button and should look like this:
Private Sub CommandButton1_Click()

End Sub

Add my code (without the "Sub CopySheet()" and the "End Sub") in
between the Sub End Sub for the commandbutton

HTH

Die_Another_Day
TimN wrote:
Thanks That worked!

I have a user form that asks for some key pieces of information to be
entered. Upon entry the data is placed in various cells and calculations
occur on that data.

Is there a way to make this procedure (the worksheet copy) occur when the
user clicks "OK" on the user form?

"Die_Another_Day" wrote:

Sub CopySheet()
Dim nSheet As Worksheet
Dim NameBox As String
NameBox = Application.InputBox("Please type a name for the new
worksheet", "Creating New Sheet", , , , , , 2)
If NameBox = "False" Then Exit Sub
Sheets("Sheet1").Copy Befo=Sheets(2)
Set nSheet = ActiveSheet
nSheet.Name = NameBox
End Sub
HTH

Die_Another_Day
TimN wrote:
I have read through several posts on this subject, but can't get my situation
to work.
I am trying to write code so that an exact copy of "Sheet1" is made and a
message box appears asking the user to name the new worksheet, for example
the new name might be TimNPayPeriod1.

How can I accomplish this?

Is the code entered in a new module or as part of the user form code?

Thanks for any help!

Duplicate worksheet creation
 

You can use a loop to make it work...
Do
NameBox = Application.InputBox("Please type a name for the _
new worksheet", "Creating New Sheet", , , , , , 2)
if NameBox = "" or NameBox = "False" then
MsgBox "I Told you to type a Name Moron" 'I love abusing the
users...
end if
loop until Not NameBox = "" or NameBox = "False"

HTH

Die_Another_Day
TimN wrote:
My bad....it works now.

Final question (I hope)

If the user closes the message box without entering a name but just presses
OK, an error message occurs: Run-time error '1004'
Method 'Name' of object_Worksheet failed

However a copy is created with a default name of "Sheet(2)". How can I
force an entry in the message box to prevent this error?

Thanks again for your assistance it has been great!

"TimN" wrote:

When I do that, I get a run time error 1004: Copy method of worksheet class
failed

What might be causing that?

"Die_Another_Day" wrote:

yes.
Double-Click your "OK" Button in the editor. This should take you to
the code for this button and should look like this:
Private Sub CommandButton1_Click()

End Sub

Add my code (without the "Sub CopySheet()" and the "End Sub") in
between the Sub End Sub for the commandbutton

HTH

Die_Another_Day
TimN wrote:
Thanks That worked!

I have a user form that asks for some key pieces of information to be
entered. Upon entry the data is placed in various cells and calculations
occur on that data.

Is there a way to make this procedure (the worksheet copy) occur when the
user clicks "OK" on the user form?

"Die_Another_Day" wrote:

Sub CopySheet()
Dim nSheet As Worksheet
Dim NameBox As String
NameBox = Application.InputBox("Please type a name for the new
worksheet", "Creating New Sheet", , , , , , 2)
If NameBox = "False" Then Exit Sub
Sheets("Sheet1").Copy Befo=Sheets(2)
Set nSheet = ActiveSheet
nSheet.Name = NameBox
End Sub
HTH

Die_Another_Day
TimN wrote:
I have read through several posts on this subject, but can't get my situation
to work.
I am trying to write code so that an exact copy of "Sheet1" is made and a
message box appears asking the user to name the new worksheet, for example
the new name might be TimNPayPeriod1.

How can I accomplish this?

Is the code entered in a new module or as part of the user form code?

Thanks for any help!