Merge simple macros to create Complex macro

[oberon.black]

I am trying to write a script to allow the information in my user form
to be placed into the next empty column, row, and then I want that
information to be used to create and name a new worksheet that will
copy its data from another existing worksheet. I will attach what I
have done and I hope some one can fix my errors. (this macro is based
on two different macros and I am needing to merge to code to create one
complex macro.). Also I get a Compile Error: Sub or Function not
defined.


Code:
--------------------

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim newSheetName As String
Set ws = Worksheet("Class GradeSheet")
newSheetName = iRow

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.TextBox56.Value) = "" Then
Me.TextBox56.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox56.Value
ws.Cells(iRow, 2).Value = Me.TextBox55.Value

'clear the data
Me.TextBox56.Value = ""
Me.TextBox55.Value = ""
Me.TextBox56.SetFocus

For Each ws In Worksheets
If ws.Name = newSheetName Or newSheetName = "" Or IsNumeric(newSheetName) Then
MsgBox "Sheet already exists or name is invalid", vbInformation
Exit Sub
End If

Next
Sheets(1).Copy before:=Sheets(1)
Sheets(1).Name = newSheetName
Sheets(newSheetName).Move After:=Sheets(Sheets.Count)
End With

End Sub

 

[Tom Ogilvy]

This modified version worked for me:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim newSheetName As String
Set ws = Worksheets("Class GradeSheet")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.TextBox56.Value) = "" Then
Me.TextBox56.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox56.Value
ws.Cells(iRow, 2).Value = Me.TextBox55.Value
newSheetName = ws.Cells(iRow, 1)

'clear the data
Me.TextBox56.Value = ""
Me.TextBox55.Value = ""
Me.TextBox56.SetFocus

For Each ws In Worksheets
If ws.Name = newSheetName Or _
newSheetName = "" Or _
IsNumeric(newSheetName) Then
MsgBox "Sheet already exists or name is invalid", vbInformation
Exit Sub
End If

Next
Sheets(1).Copy before:=Sheets(1)
Sheets(1).Name = newSheetName
Sheets(newSheetName).Move After:=Sheets(Sheets.Count)


End Sub

--
Regards,
Tom Ogilvy

"oberon.black" <[email protected]>
wrote in message

 

[oberon.black]

This code works great.


Code
-------------------

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox56.Value
ws.Cells(iRow, 2).Value = Me.TextBox55.Value
newSheetName = ws.Cells(iRow, 1)

-------------------


I this section I had to change it to

Code
-------------------

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox56.Value
ws.Cells(iRow, 2).Value = Me.TextBox55.Value
newSheetName = ws.Cells(iRow, 1) & ws.Cells(iRow, 2)

-------------------


so that it would give me both first and last name in the New WorkShee
Tab.

However, I need to insert a comma between the names in the first an
last name in the Worksheet tab. How can this be done?

I also need to know how to chang the script to identify the sheet
want to copy. The script currently copies the active sheet but I wan
it to copy another sheet that is in the workbook. How can this be don
or maybe I should say where is this done?

Thanks

 

[Tom Ogilvy]

You don't copy the activesheet, you copy the first sheet in the tab order:

Sheets(1).Copy before:=Sheets(1)


So if you have a specific sheet you want to copy

Sheets("Sheet5")).Copy before:=Sheets(1)


as an example (use the tab name of the sheet).

--
Regards,
Tom Ogilvy

"oberon.black" <[email protected]>
wrote in message
news[email protected]...

 

[Tom Ogilvy]

newSheetName = ws.Cells(iRow, 1) & ws.Cells(iRow, 2)

to

newSheetName = ws.Cells(iRow, 1) "," & ws.Cells(iRow, 2)

or to include a space

newSheetName = ws.Cells(iRow, 1) "," & ws.Cells(iRow, 2)


"oberon.black" <[email protected]>
wrote in message
news[email protected]...

 

[oberon.black]

Triied changing

newSheetName = ws.Cells(iRow, 1) & ws.Cells(iRow, 2)

to

newSheetName = ws.Cells(iRow, 1) "," & ws.Cells(iRow, 2)

or to include a space

newSheetName = ws.Cells(iRow, 1) "," & ws.Cells(iRow, 2)

But I get an error Compile: invalid seperator ao ). Are there any
other ways to do this.

 

[Dave Peterson]

Tom missed an ampersand:

newSheetName = ws.Cells(iRow, 1) & "," & ws.Cells(iRow, 2)
or
newSheetName = ws.Cells(iRow, 1) & ", " & ws.Cells(iRow, 2)

(and I think a space <bg>.)

 

[oberon.black]

Thanks works like a charm