.End(xlUp).Row + 1 not finding the last used row

[ibruzzi]

I'm trying to enter data into two sheets at once. Everything works fine except in the sheet 2, it doesn't put data on the last used row, which should be A2. Instead, it keeps adding data from A13 and below. I deleted everything in the sheet and when I enter the data, it goes to A14, repeat the same thing, it goes to A15 and so on.
what am I doing wrong here? The sheet 1 works as it's supposed to.
couldn't find an answer to this on the site.

Private Sub CommandButton2_Click()
Dim x As Long
Dim y, z As Worksheet

Set y = Sheet1
Set z = Sheet2

x = z.Range("A" & Rows.Count).End(xlUp).Row + 1
x = y.Range("A" & Rows.Count).End(xlUp).Row + 1


With z
.Cells(x, 1).Value = [Text(Now(), "MM-DD-YYYY")] 'DATE
.Cells(x, 2).Value = ComboBox3.Text 'DETAILS
.Cells(x, 3).Value = ComboBox5.Text 'DURATION
.Cells(x, 4).Value = TextBox2.Text 'WEIGHT
'.Cells(x, 5).Value = ComboBox2.Text 'TYPE
'.Cells(x, 6).Value = ComboBox1.Text 'DEPT
'.Cells(x, 8) = [Text(Now(), "DD-MM-YYYY HH:MM")]
End With

With y
.Cells(x, 1).Value = [Text(Now(), "MM-DD-YYYY")] 'DATE
.Cells(x, 2).Value = ComboBox3.Text 'DETAILS
.Cells(x, 3).Value = ComboBox5.Text 'DURATION
.Cells(x, 4).Value = TextBox2.Text 'WEIGHT
'.Cells(x, 5).Value = ComboBox2.Text 'TYPE
'.Cells(x, 6).Value = ComboBox1.Text 'DEPT
'.Cells(x, 8) = [Text(Now(), "DD-MM-YYYY HH:MM")]

End With

Unload Me
D001.Show

End Sub

 

[Fluff]

You are using x for the last row in both sheets, so it will always use the last row in sheet1

 

[ibruzzi]

You are using x for the last row in both sheets, so it will always use the last row in sheet1

I added

Dim w As Long

and replaced one "x" with "w" and it did the same. ?

Also tried

Dim w As Long
Dim x As Long

Private Sub CommandButton2_Click()
Dim x, w As Long

Dim y, z As Worksheet

Set y = Sheet1
Set z = Sheet2

w = z.Range("A" & Rows.Count).End(xlUp).Row + 1
x = y.Range("A" & Rows.Count).End(xlUp).Row + 1


With z
.Cells(x, 1).Value = [Text(Now(), "MM-DD-YYYY")] 'DATE
.Cells(x, 2).Value = ComboBox3.Text 'DETAILS
.Cells(x, 3).Value = ComboBox5.Text 'DURATION
.Cells(x, 4).Value = TextBox2.Text 'WEIGHT
'.Cells(x, 5).Value = ComboBox2.Text 'TYPE
'.Cells(x, 6).Value = ComboBox1.Text 'DEPT
'.Cells(x, 8) = [Text(Now(), "DD-MM-YYYY HH:MM")]
End With

With y
.Cells(x, 1).Value = [Text(Now(), "MM-DD-YYYY")] 'DATE
.Cells(x, 2).Value = ComboBox3.Text 'DETAILS
.Cells(x, 3).Value = ComboBox5.Text 'DURATION
.Cells(x, 4).Value = TextBox2.Text 'WEIGHT
'.Cells(x, 5).Value = ComboBox2.Text 'TYPE
'.Cells(x, 6).Value = ComboBox1.Text 'DEPT
'.Cells(x, 8) = [Text(Now(), "DD-MM-YYYY HH:MM")]

End With

Unload Me
D001.Show

End Sub

 

[Fluff]

You are still using x for the row inside both With Statements, you need to change the 1st one from x to w

 

[ibruzzi]

I added

Dim w As Long

and replaced one "x" with "w" and it did the same. ?

Also tried

Dim w As Long
Dim x As Long

Private Sub CommandButton2_Click()
Dim x, w As Long

Dim y, z As Worksheet

Set y = Sheet1
Set z = Sheet2

w = z.Range("A" & Rows.Count).End(xlUp).Row + 1
x = y.Range("A" & Rows.Count).End(xlUp).Row + 1


With z
.Cells(x, 1).Value = [Text(Now(), "MM-DD-YYYY")] 'DATE
.Cells(x, 2).Value = ComboBox3.Text 'DETAILS
.Cells(x, 3).Value = ComboBox5.Text 'DURATION
.Cells(x, 4).Value = TextBox2.Text 'WEIGHT
'.Cells(x, 5).Value = ComboBox2.Text 'TYPE
'.Cells(x, 6).Value = ComboBox1.Text 'DEPT
'.Cells(x, 8) = [Text(Now(), "DD-MM-YYYY HH:MM")]
End With

With y
.Cells(x, 1).Value = [Text(Now(), "MM-DD-YYYY")] 'DATE
.Cells(x, 2).Value = ComboBox3.Text 'DETAILS
.Cells(x, 3).Value = ComboBox5.Text 'DURATION
.Cells(x, 4).Value = TextBox2.Text 'WEIGHT
'.Cells(x, 5).Value = ComboBox2.Text 'TYPE
'.Cells(x, 6).Value = ComboBox1.Text 'DEPT
'.Cells(x, 8) = [Text(Now(), "DD-MM-YYYY HH:MM")]

End With

Unload Me
D001.Show

End Sub

oooohh.

You are still using x for the row inside both With Statements, you need to change the 1st one from x to w

ooohh I see that now lmao ?‍ my bad. totally missed that. Thank you.

 

[Fluff]

Glad to help & thanks for the feedback.