data being put on wrong line

[reddcannon]

excel 16, windows 10, desktop

I have this macro to take date from one page and put on another, it is going from tab "Pricesheet" to tab "quote" I want it to start on line 16 of tab "quote" but it keeps going to line 1. What am I doing wrong. here is what I have

Private Sub CommandButton2_Click()
Dim r As Long, s As Long
Application.ScreenUpdating = False
With Sheets("Pricesheet")
.Unprotect Password:="123"
Application.GoTo Range("a8"), True

s = Sheets("quote").Range("a" & Rows.Count).End(xlUp).Offset(1).Row
For r = 16 To 40

If .Range("a" & r) > "" Then
Sheets("quote").Range("a" & s).Resize(, 2).Value = _
.Range("a" & r).Resize(, 7).Value
s = s + 1
End If
Next
.Protect Password:="123"

End With
Application.ScreenUpdating = True
Sheets("quote").Select
End Sub

thanks in advance. I am not a genius so please don't beat me up

 

[reddcannon]

With that information try

Private Sub CommandButton2_Click()
Dim r As Long, s As Long
Application.ScreenUpdating = False
With Sheets("Pricesheet")
.Unprotect Password:="123"
Application.GoTo Range("a8"), True

r = 16
    For s = 8 To 31 ' this starts from cell A1 to last row with text
        If .Range("a" & s) > "" Then
        Range("a" & s).Resize(, 2).Select
        Sheets("quote").Range("a" & r).Resize(, 2) = Range("a" & s).Resize(, 2).Value ' starting with Row 16
        r = r + 1
        End If
Next
.Protect Password:="123"
End With
Application.ScreenUpdating = True
Sheets("quote").Select
End Sub

I missed that, so sorry, yes it now works thanks a million buddy

 

[hajiali]

No problem Glad I could help. I think you meant to mark post #7 as the solution.

 

[Herakles]

I am so sorry but you lost me. I don't see that line you say to replace. below is what you gave me and it works but only brings in column a from Pricesheet, it needs to bring in A and B from Price sheet. Sorry to be so stupid about this, but I am getting confused on what to take out and add. Can you take it out and add and send the whole thing back

Private Sub CommandButton2_Click()
Dim r As Long, s As Long
Application.ScreenUpdating = False
With Sheets("Pricesheet")
.Unprotect Password:="123"
Application.GoTo Range("a8"), True

r = 16
For s = 8 To Sheets("Pricesheet").Range("a" & Rows.Count).End(xlUp).Row ' this starts from cell A1 to last row with text
If .Range("a" & s) > "" Then
Sheets("quote").Range("a" & r).Value = .Range("a" & s).Value ' starting with Row 16
r = r + 1
End If

Next
.Protect Password:="123"
End With
Application.ScreenUpdating = True
Sheets("quote").Select
End Sub

In your original post you stated that the data was being written to the 'quotes' worksheet starting at row 1.

My suggested change was based upon your original code.