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 befo=Sheets(1)
Sheets(1).Name = newSheetName
Sheets(newSheetName).Move After:=Sheets(Sheets.Count)
End With

End Sub

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


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=399875

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 befo=Sheets(1)
Sheets(1).Name = newSheetName
Sheets(newSheetName).Move After:=Sheets(Sheets.Count)


End Sub

--
Regards,
Tom Ogilvy

"oberon.black"
wrote in message
news:oberon.black.1uhf2c_1125245120.1981@excelforu m-nospam.com...

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 befo=Sheets(1)
Sheets(1).Name = newSheetName
Sheets(newSheetName).Move After:=Sheets(Sheets.Count)
End With

End Sub

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


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile:
http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=399875

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

--
oberon.blac
-----------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...fo&userid=2673
View this thread: http://www.excelforum.com/showthread.php?threadid=39987

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

Sheets(1).Copy befo=Sheets(1)


So if you have a specific sheet you want to copy

Sheets("Sheet5")).Copy befo=Sheets(1)


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

--
Regards,
Tom Ogilvy

"oberon.black"
wrote in message
news:oberon.black.1ui1ab_1125273917.4262@excelforu m-nospam.com...

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 WorkSheet
Tab.

However, I need to insert a comma between the names in the first and
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 I
want to copy. The script currently copies the active sheet but I want
it to copy another sheet that is in the workbook. How can this be done
or maybe I should say where is this done?

Thanks.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile:
http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=399875

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"
wrote in message
news:oberon.black.1ui1ab_1125273917.4262@excelforu m-nospam.com...

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 WorkSheet
Tab.

However, I need to insert a comma between the names in the first and
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 I
want to copy. The script currently copies the active sheet but I want
it to copy another sheet that is in the workbook. How can this be done
or maybe I should say where is this done?

Thanks.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile:
http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=399875

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.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=399875

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" wrote:

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.

--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=399875

--

Dave Peterson