If, Then, Copy, Paste, Next Loop

[ljknight]

Hi Everyone!

I have a spreadsheet with survey results. One question has an "other" option. If the survey taker chose other, column P gets an "X" in it and what they filled in comes up in column Q.

So, I want to do the following: If P2 = "X", then offset 1 (to Q2), copy contents of Q2, paste over P2, else check P3 -- loop until P# is blank.

My columns will always be constant, but the row count depends on the number of survey responses received and I do not want to have to make adjustments manually for the fluctuation in row count.

ALSO, since I always want it ALL...after the loop is complete, I want to select all the rows and copy and append the copied rows to another worksheet which which will have all the results collected for the year on it. Since the number of rows can fluctuate month to month, how do I automatically select all the active rows in worksheet one, and how do I get them pasted to the next blank row in worksheet two?

Thank you!

Laura

[jimboy]

Hi - try this, you will need to change the sheet name you want the data copied to (twice)- it only copies the line with an X in column P

Sub test()
Dim c As Range
Dim myrow, row
For Each c In Range("P2:P" & Range("P65536").End(xlUp).row)
If c.Text = "X" Then
c = c.Offset(0, 1)
'Amend the sheet name here and...                    ...here
c.EntireRow.Copy Sheets("Sheet2").Range("A" & Sheets("Sheet2").Range("P65536").End(xlUp).Offset(1, 0).row)

End If
Next

End Sub