copy a sheet in front and rename gets a 1004 runtime error

[Excel-General]

I copy a control worksheet sheet(2) from the active workbook it places
it before the control sheet. I want to change the name of the new
sheet. The last line where I want to assign the name gives me a 1004
runtime error. Can you tell me how to name the sheet? the copied
sheet should now be sheet(2) right? tia,

Sub CpySinglePatientSht()
Dim wb As Workbook
Dim ws As Worksheet
Dim Lname As String
Dim sStr As String


Set wb = ThisWorkbook

wb.Sheets(2).Copy before:=wb.Sheets(2)
'copies the control worksheet and puts the copied sheet in front
of it
Set ws = wb.Sheets(2)
sStr = UserForm1.g_fNameLName
Lname = Mid(sStr, InStr(1, sStr, " ") + 1)
Debug.Print Lname
'parses the FName and LName concatenation into just the Lname and
renames worksheet
wb.Sheets(2).Name = Lname


End Sub

 

[Excel-General]

I am enclosing the code for the userform in case that is why I am
getting the 1004 error.
thanks very much,

Sub UserForm_Initialize()

Dim lngLastRow As Long
Dim ws As Worksheet
Dim c As Range
Dim rng As Range


Set ws = ThisWorkbook.Worksheets("patients")
'lngLastRow = Cells.Find(What:="*", After:=ws.Range("C1"), _
SearchDirection:=xlPrevious).Row


lngLastRow = ws.Cells.Find("*", SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row
Debug.Print lngLastRow


Set rng = ws.Range("C1:C" & lngLastRow)

For Each c In rng.Cells
Me.ComboBox2.AddItem c.Value

Next c

Me.ComboBox2.AddItem "All"
Me.ComboBox2.AddItem "Exit"

End Sub

 

[Excel-General]

Sorry, wrong one:

Public Sub ComboBox2_change()

Select Case ComboBox2.Value
Case Is = "All"


cpyAllPatientsShts


Case Is = "Exit"

'do nothing

Case Else

g_fNameLName = UserForm1.ComboBox2.Value
CpySinglePatientSht
SingleMonths
End Select

Unload Me
Me.ComboBox2.Clear
End Sub

 

[Janis R.]

I can't mark this answered in google however, the request has changed
and I no longer need to copy all the sheets to the same worksheet only
print them so the question is answered.

 

[Janis R.]

What I need for is it to print. this print script prints and then
deletes the sheet. I don't get the 1004 runtime error message. My
question is how do I get it to delete without the dialog each time.
There are 50 sheets to print. The other question is how do I set it
in landscape mode. Tia,
janis

Public Sub prntAllPatientsShts()
Dim lngLastRow As Long
Dim ws As Worksheet
Dim wb As Workbook
Dim c As Range
Dim rng As Range
Dim sStr As String, Lname As String
Dim inputDate As String

Set wb = ThisWorkbook
Set ws = wb.Worksheets("patients")

lngLastRow = ws.Cells.Find("*", SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row

Set rng = ws.Range("C1:C" & lngLastRow)

inputDate = InputBox("Enter a date:", "Date", Date, 100, 100)

For Each c In rng.Cells

wb.Sheets(2).Copy before:=wb.Sheets(2)
wb.Save
Set ws = wb.Sheets(2)

ws.Range("T5") = inputDate
ws.Range("A4") = c.Cells
sStr = c.Value
Lname = Mid(sStr, InStr(1, sStr, " ") + 1)
ws.Name = Lname

'With ActiveSheet.PageSetup.PrintOut
'
' .Orientation = xlLandscape
'
'End With

ws.PrintOut
ws.Delete

Next c
End Sub

 

[Janis R.]

Sorry, I got the answers. It works now. Thanks anyways,