Using VBA to create a new worksheet, and then target new worksheet

[brianproctorla]

Hello,
I have read numerous threads about creating a new worksheet, and havin
the user enter the new name for the worksheet. I would like to know ho
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 woul
like to target this worksheet and paste the data.

The result would be that everytime this macro is run, a new mutuall
exclusive worksheet is created, and named by a user. This can be don
over and over again, until there are multiple worksheets
Thanks,
-Bria

 

[Dave Peterson]

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