You can copy the worksheet or you can copy the cells on the worksheet and paste
to the new worksheet.
The first example inserts a new worksheet and copies data from a worksheet named
Master:
Option Explicit
Sub testme1()
Dim NewWks As Worksheet
Dim OldWks As Worksheet
Dim NewName As String
Set OldWks = Worksheets("Master")
NewName = Trim(InputBox(prompt:="what do you want to call it?"))
If NewName = "" Then
Exit Sub
End If
Set NewWks = Worksheets.Add
On Error Resume Next
NewWks.Name = NewName
If Err.Number < 0 Then
MsgBox "Something went wrong with the naming!" & vbLf & _
"Please change: " & NewWks.Name & " to what you want."
Err.Clear
End If
OldWks.Cells.Copy _
Destination:=NewWks.Range("a1")
End Sub
This second example copies the sheet and renames it:
Option Explicit
Sub testme2()
Dim NewWks As Worksheet
Dim OldWks As Worksheet
Dim NewName As String
NewName = Trim(InputBox(prompt:="what do you want to call it?"))
If NewName = "" Then
Exit Sub
End If
Set OldWks = Worksheets("Master")
OldWks.Copy after:=OldWks
Set NewWks = ActiveSheet
On Error Resume Next
NewWks.Name = NewName
If Err.Number < 0 Then
MsgBox "Something went wrong with the naming!" & vbLf & _
"Please change: " & NewWks.Name & " to what you want."
Err.Clear
End If
End Sub
brianproctorla wrote:
Hello,
I have read numerous threads about creating a new worksheet, and having
the user enter the new name for the worksheet. I would like to know how
to target this new worksheet.
Example.
I would like to run a MACRO that copies data from a set worksheet,
creates a new worksheet and prompts the user for a name. Then I would
like to target this worksheet and paste the data.
The result would be that everytime this macro is run, a new mutually
exclusive worksheet is created, and named by a user. This can be done
over and over again, until there are multiple worksheets
Thanks,
-Brian
--
brianproctorla
------------------------------------------------------------------------
brianproctorla's Profile: http://www.excelforum.com/member.php...o&userid=27567
View this thread: http://www.excelforum.com/showthread...hreadid=470896
--
Dave Peterson