Macro to create a new worksheet that is a copy of an existing one?

[Guest]

I have created a button that I want to assign a macro to that will take the
information in a cell and make it the name of a new worksheet. This
worksheet needs to be a copy of an existing worksheet (#2 in position in the
line of worksheets currently) but with the name the user enters in the box
provided. Any suggestions?
-Kai

 

[Leith Ross]

I have created a button that I want to assign a macro to that will take the
information in a cell and make it the name of a new worksheet. This
worksheet needs to be a copy of an existing worksheet (#2 in position in the
line of worksheets currently) but with the name the user enters in the box
provided. Any suggestions?
-Kai

Hello Kal,

This macro use the value in cell A1 to rename the second worksheet (#2
in position).

Sub RenameSheet()
Worksheets(2).Name = ActiveSheet.Range("A1").Name
End Sub

Sincerely,
Leith Ross

 

[Leith Ross]

I have created a button that I want to assign a macro to that will take the
information in a cell and make it the name of a new worksheet. This
worksheet needs to be a copy of an existing worksheet (#2 in position in the
line of worksheets currently) but with the name the user enters in the box
provided. Any suggestions?
-Kai

Hello Kal,

This macro use the value in cell A1 to rename the second worksheet (#2
in position).

Sub RenameSheet()
Worksheets(2).Name = ActiveSheet.Range("A1").Text
End Sub

Sincerely,
Leith Ross

 

[tissot.emmanuel]

Hi,

Try this sub:

Sub AddNewSheet()
Dim SheetName As String
SheetName = Range("A1").Value
Sheets(2).Copy after:=Sheets(Sheets.Count)
On Error GoTo ErrHandler:
ActiveSheet.Name = SheetName
Exit Sub
ErrHandler:
MsgBox SheetName & " is not a valid sheet name or is already used."
End Sub

Regards,

Manu/