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

 

[hajiali]

not sure if this is what you need but try this

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 = 1 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

 

[reddcannon]

not sure if this is what you need but try this

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 = 1 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

well if did work that it is puting it on the right line, line 16, but now your code is bringing in 4 lines too many from top

 

[reddcannon]

well if did work that it is puting it on the right line, line 16, but now your code is bringing in 4 lines too many from top

and I should of said nothing past line 31 on pricesheet. I figure out the first part, I changed the s = 1 to S = 8

 

[reddcannon]

and you code works but it is only bringing in column A from pricesheet and should be column a and b like mine did

 

[Herakles]

Just change this line

s = Sheets("quote").Range("a" & Rows.Count).End(xlUp).Offset(1).Row

to

s = 16

 

[hajiali]

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

 

[reddcannon]

Just change this line

s = Sheets("quote").Range("a" & Rows.Count).End(xlUp).Offset(1).Row

to

s = 16

 

[reddcannon]

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

 

[hajiali]

did you try my code on post #7