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

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