Still adding to Complex Macro...1 step at a time.

[oberon.black]

I need help linking to macros together.

the first macro is:

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

Function SheetName(rCell As Range, Optional UseAsRef As Boolean) As String
Application.Volatile
If UseAsRef = True Then
SheetName = "'" & rCell.Parent.Name & "'!"
Else
SheetName = rCell.Parent.Name
End If
End Function

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


this will allow me to add the name of the worksheet to a cell within
the worksheet.

the second macro is:

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

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 last name"
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) & "'" & ws.Cells(iRow, 2)

'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("Student Sheet").Copy before:=Sheets(1)
Sheets(1).Name = newSheetName
Sheets(newSheetName).Move After:=Sheets(Sheets.Count)

'close userform
Unload Me
End Sub

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


This macro is the one that I use to create and name the worksheet.

Can someone please help me merge these macros. This is a problem that
I have a lot of times. Does any one know of a book or guide that can
show me how to merge macros?

 

[Dnereb]

To be honest or plain blunt I can not see your problem.
Google for Excel and Book or go to some computer dedicated site like
www.comcol.nl (dutch site)
and buy a book.

to merge macro's copy the first macro without the sub... and end sub
line
and past it just befor or after the code in the function or if you want
to run the functioon within the sub
place

dim Str as String

Str = Sheetnam ( YourRange,[Yourreference])

at the start or end of the sub where Yourrange is a range an
Yourreference = True if you want it to do something