Return Data to next empty line of Worksheet

[mssianlf]

Help!!
I have been tasked with making a user form that gathers basic information and enters in into an excel worksheet, I have created and coded the below but cant make the data return to the next empty line it just over writes the previous entry. I have included the whole code.As I don't really know what I am doing any help would be gratefully received but if it could be explained in small simple words I would be grateful.

Private Sub cmdcancel_Click()
Unload Me
End Sub

Private Sub cmdclear_Click()
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

Private Sub cmdok_Click()

Dim RowCount As Long
Dim Control As Control

If Me.txtCRM.Value = "" Then
MsgBox "Please enter a CRM Name.", vbExclamation, "txtinitialyear"
Me.txtCRM.SetFocus
Exit Sub
End If
If Me.txtconame.Value = "" Then
MsgBox "Please enter a Company Name.", vbExclamation, "txtcontactname"
Me.txtconame.SetFocus
Exit Sub
End If
If Me.txtemail.Value = "" Then
MsgBox "Please enter an Email Address.", vbExclamation, "txtphone"
Me.txtemail.SetFocus
Exit Sub
End If
If Not IsNumeric(Me.txtyearvalue.Value) Then
MsgBox "The Year Value box must contain a number.", vbExclamation, "txtdealvalue"
Me.txtyearvalue.SetFocus
Exit Sub
End If
If Not IsNumeric(Me.txtdealvalue.Value) Then
MsgBox "The Deal Value box must contain a number.", vbExclamation, "chksplitpayment"
Me.txtdealvalue.SetFocus
Exit Sub
End If
'find first empty row in database'
RowCount = Worksheets("Egold Invoices Payment Details").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Egold Invoices").Range("A2")
.Offset(RowCount, 0).Value = Me.txtCRM.Value
.Offset(RowCount, 1).Value = Me.txtconame.Value
.Offset(RowCount, 2).Value = Me.txtcontactname.Value
.Offset(RowCount, 3).Value = Me.txtemail.Value
.Offset(RowCount, 4).Value = Me.txtphone.Value
.Offset(RowCount, 5).Value = Me.cobxegoldtype.Value
.Offset(RowCount, 15).Value = Me.txtyearvalue.Value
.Offset(RowCount, 16).Value = Me.txtdealvalue.Value
.Offset(RowCount, 17).Value = DateValue(Me.calstartdate.Value)
.Offset(RowCount, 18).Value = DateValue(Me.calenddate.Value)
.Offset(RowCount, 19).Value = Me.txtinitialyear.Value
.Offset(RowCount, 20).Value = Me.txtnotes.Value
If Me.chkgenhr.Value = True Then
.Offset(RowCount, 6).Value = "P"
Else
.Offset(RowCount, 6).Value = "X"
End If
If Me.chkukcb.Value = True Then
.Offset(RowCount, 7).Value = "P"
Else
.Offset(RowCount, 7).Value = "X"
End If
If Me.chkuktd.Value = True Then
.Offset(RowCount, 8).Value = "P"
Else
.Offset(RowCount, 8).Value = "X"
End If
If Me.chkrecres.Value = True Then
.Offset(RowCount, 9).Value = "P"
Else
.Offset(RowCount, 9).Value = "X"
End If
If Me.chkfd.Value = True Then
.Offset(RowCount, 10).Value = "P"
Else
.Offset(RowCount, 10).Value = "X"
End If
If Me.chkboard.Value = True Then
.Offset(RowCount, 11).Value = "P"
Else
.Offset(RowCount, 11).Value = "X"
End If
If Me.chkukall.Value = True Then
.Offset(RowCount, 12).Value = "P"
Else
.Offset(RowCount, 12).Value = "X"
End If
If Me.chkeurohr.Value = True Then
.Offset(RowCount, 13).Value = "P"
Else
.Offset(RowCount, 13).Value = "X"
End If
If Me.chknew.Value = True Then
.Offset(RowCount, 14).Value = "N"
Else
.Offset(RowCount, 14).Value = ""
End If
If Me.chkrenew.Value = True Then
.Offset(RowCount, 14).Value = "R"
Else
.Offset(RowCount, 14).Value = ""
End If
If Me.chkwinback.Value = True Then
.Offset(RowCount, 14).Value = "W"
Else
.Offset(RowCount, 14).Value = ""
End If
With Worksheets("Egold Invoices Payment Details").Range("A2")
If Me.chksplitpayment.Value = True Then
.Offset(RowCount, 0).Value = "Yes"
Else
.Offset(RowCount, 0).Value = "No"
End If
.Offset(RowCount, 1).Value = Me.txtsp1.Value
.Offset(RowCount, 2).Value = Me.txtsp2.Value
.Offset(RowCount, 3).Value = Me.txtsp3.Value
.Offset(RowCount, 4).Value = Me.txtsp4.Value
.Offset(RowCount, 5).Value = Me.txtsp5.Value
.Offset(RowCount, 6).Value = Me.txtsp6.Value
.Offset(RowCount, 7).Value = Me.txtsp7.Value
.Offset(RowCount, 8).Value = Me.txtsp8.Value
.Offset(RowCount, 9).Value = Me.txtsp9.Value
.Offset(RowCount, 10).Value = Me.txtsp10.Value
.Offset(RowCount, 11).Value = Me.txtsp11.Value
.Offset(RowCount, 12).Value = Me.txtsp12.Value
.Offset(RowCount, 7).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")
End With
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End With
End Sub

 

[mssianlf]

Not sure really.
I was trying to have data going to two separate sheets but this in not really required just me trying to e clever. Should I try take out the 'payment information sheets bits and see if that works?
Sian

 

[VoG]

I think that I would have coded it like this

With Worksheets("Egold Invoices")
    RowCount = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Cells(RowCount, 1).Value = Me.txtCRM.Value
    .Cells(RowCount, 2).Value = Me.txtconame.Value
    .Cells(RowCount, 3).Value = Me.txtcontactname.Value
'and so on

 

[mssianlf]

Hi,
Thank You soo much. It is all working now. I took your advice and changed all the offset's to cells and it is has worked!

Thank you again for your help and patience with a novice