Write record from Form to new row in spreadsheet

[Tony]

I am using a form to provide input into a spreadsheet. Every time I write a
record, I want it to write to a new line in the spreadsheet. When I first
used this code, it wrote on the second line in the spreadsheet, even though
there was a second header line there and it overwrote the line. When I took
out the first header, it wrote on the second line okay. However, when I tried
to enter a new record, it overwrote the first record.

Basically I want to write the first new record on the third line (after two
header lines) and then each new record on the next available line, etc. Below
is the code I am using. Can you someone help me out here?

Private Sub CommandButton8_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Master")
Dim nextrow As Long

'find first empty row in database
With Worksheets("Master")
nextrow = .Range("p65536").End(xlUp).Row + 1
End With
'check to see if there's room
If nextrow >= 6589 Then
MsgBox "out of room!"
Exit Sub
End If
With Worksheets("Master")
.Cells(nextrow, 1).Value = InquiryDate
.Cells(nextrow, 2).Value = DatabaseAccessed
.Cells(nextrow, 3).Value = Requestor
.Cells(nextrow, 4).Value = AccountAccessed
.Cells(nextrow, 5).Value = RequestReason
.Cells(nextrow, 6).Value = InformationRequested
.Cells(nextrow, 7).Value = Comments
End With
End Sub

 

[Mike H]

Tony,

The problem is your getting NextRow from column P but you aren't writing to
column p so nextRow never changes.

get nextrow from column A with this

With Worksheets("Master")
nextrow = .Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
End With
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.