Looping code pasting onto same line

[mramos17]

New to excel/vba, not too familiar with coding, & many other generic openers you've seen.
I'm using a loop to copy an entire row to another sheet if a criteria is met but my code keeps pasting the data into the same row, overwriting the previous entry. Not sure if the EntireRow.Delete argument is necessary, I was following a tutorial and they had said that looping from the bottom up is preferred & deleting rows as the data is copied is necessary for this loop to work.

____________________________________________________________________
Context:
Sheet = Report - Has data
Sheet = final - Destination for the copied rows
____________________________________________________________________


Sub Loop_Macro()

'Find last row
LR = Sheets("report").Range("B" & Rows.Count).End(xlUp).Row

'step 2 - sets spacing between rows read
For I = LR To 100 Step -1

'Find last row on destination sheet
Sheet1_LR = Sheets("final").Cells(Rows.Count, 1).End(xlUp).Row

'Logic statements for copying
If Sheets("report").Cells(I, 2).Value = "Pay Type" Then
Sheets("report").Cells(I, 1).EntireRow.Copy Sheets("final").Cells(Sheet1_LR + 1, 1).Offset(-1, 0)
Sheets("report").Cells(i, 1).EntireRow.Delete
End If

Next I
End Sub

 

[rlv01]

To start with, replace this

If Sheets("report").Cells(I, 2).Value = "Pay Type" Then

with this

 If Sheets("report").Cells(1, 2).Value = "Pay Type" Then

and this

Sheets("report").Cells(I, 1).EntireRow.Copy Sheets("final").Cells(Sheet1_LR + 1, 1).Offset(-1, 0)

with this

Sheets("report").Cells(I, 1).EntireRow.Copy Sheets("final").Cells(Sheet1_LR + 1, 1)

and reassess.

(Tip: For future posts , you should try to use code tags like I did above when posting your code. It makes it easier to read.)

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[mramos17]

Wonderful, I appreciate it. Can you explain the reasoning behind changing the I to a 1 within the first If statement? I'm trying to get away from copy/pasting code & have an understanding of what I'm doing.

 

[rlv01]

Wonderful, I appreciate it. Can you explain the reasoning behind changing the I to a 1 within the first If statement? I'm trying to get away from copy/pasting code & have an understanding of what I'm doing.

Your code indicates you only want to do the copy if the column header in column 2 (Col B) is "Pay Type". That information is ALWAYS going to be in row #1 so the statement checking to see what the column header is must be Cells(1,2), not Cells(I,2) since I is the looping variable and thus will change.