I have a loop that starts like this:

For Each i In Range("Data")

Data is a single colum.

If I wanted to copy a specific row that i was currently referencing, how
would I do that? This is driving me crazy.

Thanks,

Mike

"ThisShouldBeEasy" wrote:

I have a loop that starts like this:

For Each i In Range("Data")

Data is a single colum.

If I wanted to copy a specific row that i was currently referencing, how
would I do that? This is driving me crazy.

Thanks,

Mike

Assuming you want to copy to another sheet

For Each i In Range("Data")
if i = "ref" then
With Worksheet("Sheet2")
set dest=cells( ,rows.count,"A").end(xlup)
end with
i.entireRow.copy
End if
next

regards
Peter

Peter,

That helped a lot. One more question.

set dest=cells(rows.count,"A").end(xlup)

This gives me the last used cell, I need the row after that. I tried + 1
but that would be easy... no dice. What do I need to do?

Thanks again,

Mike


"PeterAtherton" wrote:



"ThisShouldBeEasy" wrote:

I have a loop that starts like this:

For Each i In Range("Data")

Data is a single colum.

If I wanted to copy a specific row that i was currently referencing, how
would I do that? This is driving me crazy.

Thanks,

Mike

Assuming you want to copy to another sheet

For Each i In Range("Data")
if i = "ref" then
With Worksheet("Sheet2")
set dest=cells( ,rows.count,"A").end(xlup)
end with
i.entireRow.copy
End if
next

regards
Peter

That's because its out of the range defined by "Data".
Therefore, you should do something like

dim x as long

x = rows.count
then:
set dest=cells(x+1,"A").end(xlup)

Cheers
AShley


"ThisShouldBeEasy" wrote:

Peter,

That helped a lot. One more question.

set dest=cells(rows.count,"A").end(xlup)

This gives me the last used cell, I need the row after that. I tried + 1
but that would be easy... no dice. What do I need to do?

Thanks again,

Mike


"PeterAtherton" wrote:



"ThisShouldBeEasy" wrote:

I have a loop that starts like this:

For Each i In Range("Data")

Data is a single colum.

If I wanted to copy a specific row that i was currently referencing, how
would I do that? This is driving me crazy.

Thanks,

Mike

Assuming you want to copy to another sheet

For Each i In Range("Data")
if i = "ref" then
With Worksheet("Sheet2")
set dest=cells( ,rows.count,"A").end(xlup)
end with
i.entireRow.copy
End if
next

regards
Peter

For Each i In Range("Data")
if i = "ref" then
With Worksheet("Sheet2")
set dest=.cells(.rows.count,"A").end(xlup).offset(1,0)
end with
i.entireRow.copy
End if
next i

I added some dots so that the ranges refered to the previous With object
(sheet2).

ThisShouldBeEasy wrote:

Peter,

That helped a lot. One more question.

set dest=cells(rows.count,"A").end(xlup)

This gives me the last used cell, I need the row after that. I tried + 1
but that would be easy... no dice. What do I need to do?

Thanks again,

Mike

"PeterAtherton" wrote:



"ThisShouldBeEasy" wrote:

I have a loop that starts like this:

For Each i In Range("Data")

Data is a single colum.

If I wanted to copy a specific row that i was currently referencing, how
would I do that? This is driving me crazy.

Thanks,

Mike

Assuming you want to copy to another sheet

For Each i In Range("Data")
if i = "ref" then
With Worksheet("Sheet2")
set dest=cells( ,rows.count,"A").end(xlup)
end with
i.entireRow.copy
End if
next

regards
Peter

--

Dave Peterson