I have the code below. It is suppose to empty the info into both th
'CGS' and 'AT' worksheet. 'CGS' gets the info in the right cells bu
the 'AT' worksheet does not get anything dropped in it.

Please review the code and tell me were the problem is. Please give o
show line of code to be added or removed.

Please be as simple in your details as possible. missing code i
welcome.


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

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim newSheetName As String
Dim ws1 As Worksheet
Dim iRow1 As Long
Set ws = Worksheets("CGS")
Set ws1 = Worksheets("AT")

'find first empty row in AT, column 2
If ws1.Range("B10").Value = "" Then
iRow1 = ws1.Cells(Rows.Count, 2) _
.End(xlUp).Row + 9 'adjust if there is data _'in B1:B9
Else
iRow1 = ws1.Cells(Rows.Count, 2) _
.End(xlUp).Row + 1
End If

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

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

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.LstNm.Value
ws.Cells(iRow, 5).Value = Me.FrstNm.Value
newSheetName = ws.Cells(iRow, 1) & "," & ws.Cells(iRow, 5)

'clear the data
Me.LstNm.Value = ""
Me.FrstNm.Value = ""
Me.LstNm.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").Visible = xlSheetVisible
Sheets("Student Sheet").Copy befo=Sheets(1)
Sheets("Student Sheet").Visible = xlSheetVeryHidden
Sheets(1).Name = newSheetName
Sheets(newSheetName).Move After:=Sheets(Sheets.Count)

'close userform
Unload Me

'copy data to AT (ws1) at the same time you copy to database.
Worksheets("CGS").Activate

End Sub

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

--
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=46696