VBA assistance - form data not posting to next cell, continues to overwrite

[KayWill]

I have another issue where I'm stumped, I've used this same code on 5 different other forms and it performs as it should on them but on my current worksheet, it continues to "overwrite" the previous data instead of moving to the next line in the table. My code is as follows:

On Error Resume Next

' Write data to worksheet
    RowCount = Worksheets("CoreConSummary").Range("AH1").CurrentRegion.Rows.Count
    With Worksheets("CoreConSummary").Range("AH1")
        .Offset(RowCount, 0).Value = Me.txt_SiteWrk.Value
        .Offset(RowCount, 1).Value = Me.txt_VESTIBULE.Value
        .Offset(RowCount, 2).Value = Me.txt_CHKOUT.Value
        .Offset(RowCount, 3).Value = Me.txt_PHOTOLAB.Value
        .Offset(RowCount, 4).Value = Me.txt_MINCLINIC.Value
        .Offset(RowCount, 5).Value = Me.txt_RETAIL.Value
        .Offset(RowCount, 6).Value = Me.txt_SOA.Value
        .Offset(RowCount, 7).Value = Me.txt_PHARMACY.Value
        .Offset(RowCount, 8).Value = Me.txt_EMPAREA.Value
        .Offset(RowCount, 9).Value = Me.txt_RECVAREA.Value
        .Offset(RowCount, 10).Value = Me.txt_RRMS.Value
        .Offset(RowCount, 11).Value = Me.txt_GENCOND.Value
        .Offset(RowCount, 12).Value = Me.txt_PROFIT.Value
        .Offset(RowCount, 13).Value = Format(Now, "mm/dd/yyyy hh:nn:ss")
    End With

' Clear the form
    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

The only difference with this form is that I change the numbers entered into percentages, code example as follows:

Private Sub txt_SiteWrk_AfterUpdate()
txt_SiteWrk.Value = Format(txt_SiteWrk.Value / 100, "0.0%")
End Sub

but this 'should' have nothing to do with the information from the user pasting in on a separate line what am I missing?

 

[AlphaFrog]

Add 1 to RowCount to make it the next empty line.

RowCount = Worksheets("CoreConSummary").Range("AH1").CurrentRegion.Rows.Count + 1

 

[KayWill]

Thank you for replying Alphafrog, I'll try this and let you know the outcome.

 

[KayWill]

Add 1 to RowCount to make it the next empty line.

RowCount = Worksheets("CoreConSummary").Range("AH1").CurrentRegion.Rows.Count + 1

Hi AlphaFrog, finally got it working. Just to update you, when I placed the '+1' after count, it dropped it too far down which was outside of the table. Don't know why Excel is being so quirky with me today but I did get it to work by changing my code to the following:

On Error Resume Next

' Write data to worksheet
  Set ws = Worksheets("CoreConSummary")
  LRow = ws.Cells(Rows.Count, 34).End(xlUp).Offset(0, 34).Row
    
    With ws
        .Cells(LRow, 34).Value = Me.txt_SiteWrk.Value
        .Cells(LRow, 35).Value = Me.txt_VESTIBULE.Value
        .Cells(LRow, 36).Value = Me.txt_CHKOUT.Value
        .Cells(LRow, 37).Value = Me.txt_PHOTOLAB.Value
        .Cells(LRow, 38).Value = Me.txt_MINCLINIC.Value
        .Cells(LRow, 39).Value = Me.txt_RETAIL.Value
        .Cells(LRow, 40).Value = Me.txt_SOA.Value
        .Cells(LRow, 41).Value = Me.txt_PHARMACY.Value
        .Cells(LRow, 42).Value = Me.txt_EMPAREA.Value
        .Cells(LRow, 43).Value = Me.txt_RECVAREA.Value
        .Cells(LRow, 44).Value = Me.txt_RRMS.Value
        .Cells(LRow, 45).Value = Me.txt_GENCOND.Value
        .Cells(LRow, 46).Value = Me.txt_PROFIT.Value
        .Cells(LRow, 47).Value = Format(Now, "mm/dd/yyyy hh:nn:ss")
    End With

I thought that maybe is because my paste were in cells 34 that I needed to use the .Cells line of code to direct the paste to the correct columns. It's working and I want to say THANK YOU very much for responding. Hopefully my notes above will help others having similar challenges.

 

[KayWill]

Re: SOLVED - VBA assistance - form data not posting to next cell, continues to overwrite

See previous response from me to see how this was resolved.