Debug - last leg of project

[oberon.black]

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 before:=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

 

[Rowan]

You probably need to add some code to the section:

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

eg

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

Hope this helps
Rowan

 

[oberon.black]

I have updated the code to with this.


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

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

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


It puts the userform info into the 'AT' page butinstead of starting on
'B10' it starts on 'B12'.

What do I need to do to fix this?

 

[Rowan]

You need to look at how you are setting the variable iRow1 in this section:

'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

I can't quite follow the logic here. If B10 is empty then iRow1 is set
to be the last used cell in column B + 9 otherwise it is the last used
cell + 1.

Regards
Rowan

 

[oberon.black]

I dont follow what you mean please explain yourself?

 

[Rowan]

Apologies, the problem was not neccesarily to do with the setting of
iRow1 but rather that you didn't use the correct row variable when
inserting data onto the AT sheet:

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

should read

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

Regards
Rowan