How to do End(xlUp).Row + 1 using a loop in VBA

[nkw2306]

HI. So I am using a loop to move data from one sheet to another but there are 3 sheets in all. I need it to look up each sheet before it moves data over to only paste in a blank cell.

Currently my macro is as follows

Rc = 2
For p = 1 To 3
For i = 5 To 3000
With Sheets("Data")
If Sheets("Data").Range("A" & i).Value = "" Then GoTo next_i
If Sheets("Data").Range("M" & i).Value = Sheets("Lookup").Range("P" & p).Value Then
Worksheets(Sheets("Data").Range("M" & i).Value).Range("A" & Rc).Value = Sheets("Data").Range("A" & i).Value

However I want Rc to be the end of the row on the individual tabs as the information is always in different rows.

Please help!!!

 

[Fluff]

How about

For p = 1 To 3
   For i = 5 To 3000
      With Sheets("Data")
         If .Range("A" & i).Value = "" Then GoTo next_i
         If .Range("M" & i).Value = Sheets("Lookup").Range("P" & p).Value Then
            Worksheets(.Range("M" & i).Value).Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Data").Range("A" & i).Value

 

[nkw2306]

You Legend Fluff!!!!

Thanks so much!

How about

For p = 1 To 3
   For i = 5 To 3000
      With Sheets("Data")
         If .Range("A" & i).Value = "" Then GoTo next_i
         If .Range("M" & i).Value = Sheets("Lookup").Range("P" & p).Value Then
            Worksheets(.Range("M" & i).Value).Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Data").Range("A" & i).Value

 

[Fluff]

You're welcome & thanks for the feedback