In this line of code I want to analyse each cell in the range including A2
to the last non-blank cell in column A:

For Each rcd In Worksheets("Data").Range("A2", Selection.End(xlDown))

How do I specify this range without selecting anything?

I've tried (apart from what's above) .Range("A2", Range("A:A").End(xlDown))
which also doesn't work

Any help greatly appreciated

cheers,

Damien

Damien McBain wrote:

In this line of code I want to analyse each cell in the range including A2
to the last non-blank cell in column A:

For Each rcd In Worksheets("Data").Range("A2", Selection.End(xlDown))

How do I specify this range without selecting anything?

I've tried (apart from what's above) .Range("A2", Range("A:A").End(xlDown))
which also doesn't work

Here's the whole thing:

Sub ProcessData()
=================================
For Each rt In Worksheets("Data").Range("A2", Range("A2").End(xlDown))

With Worksheets.Add
.Name = rt
End With
For Each rcd In Worksheets("Data").Range("A2", _
Range("A2").End(xlDown))
If rcd.Value = rt.Value Then
rcd.EntireRow.Copy
Worksheets(rt).Range("A2", Selection.End(xlDown) + 1).Paste
Else
End If
Next rcd

Next rt
End Sub
=================================

It runs fine up till the line after the first "End With". Is the problem my
range selection statement or the way I'm nesting the For...Next?

Voila

For Each rcd In Worksheets("Data").Range("A2",
Worksheets("Data").Range("A2").End(xlDown))
Regards
JY

"Damien McBain" wrote in message
...
In this line of code I want to analyse each cell in the range including A2
to the last non-blank cell in column A:

For Each rcd In Worksheets("Data").Range("A2", Selection.End(xlDown))

How do I specify this range without selecting anything?

I've tried (apart from what's above) .Range("A2",
Range("A:A").End(xlDown))
which also doesn't work

Any help greatly appreciated

cheers,

Damien

In : For Each rt In Worksheets("Data").Range("A2", Range("A2").End(xlDown))
Worksheets("Data").Range("A2" : Refer to A2 on Worksheets("Data")
Range("A2").End(xlDown) Refer to all cell in column A on the active sheet,.
ie new wk RT

Therfor you have to repeat the sheet name : Worksheets("Data").Range("A2",
Worksheets("Data").Range("A2").End(xlDown))
Regards
JY


"Damien McBain" wrote in message
...
Damien McBain wrote:

In this line of code I want to analyse each cell in the range including
A2
to the last non-blank cell in column A:

For Each rcd In Worksheets("Data").Range("A2", Selection.End(xlDown))

How do I specify this range without selecting anything?

I've tried (apart from what's above) .Range("A2",
Range("A:A").End(xlDown))
which also doesn't work

Here's the whole thing:

Sub ProcessData()
=================================
For Each rt In Worksheets("Data").Range("A2", Range("A2").End(xlDown))

With Worksheets.Add
.Name = rt
End With
For Each rcd In Worksheets("Data").Range("A2", _
Range("A2").End(xlDown))
If rcd.Value = rt.Value Then
rcd.EntireRow.Copy
Worksheets(rt).Range("A2", Selection.End(xlDown) + 1).Paste
Else
End If
Next rcd

Next rt
End Sub
=================================

It runs fine up till the line after the first "End With". Is the problem
my
range selection statement or the way I'm nesting the For...Next?

Jean-Yves wrote:

In : For Each rt In Worksheets("Data").Range("A2", Range("A2").End(xlDown))
Worksheets("Data").Range("A2" : Refer to A2 on Worksheets("Data")
Range("A2").End(xlDown) Refer to all cell in column A on the active sheet,.
ie new wk RT

Therfor you have to repeat the sheet name : Worksheets("Data").Range("A2",
Worksheets("Data").Range("A2").End(xlDown))
Regards
JY

Cheers JY, thanks to you that part of the code is working now. The bit I'm
having trouble with now is pasting into the new sheet. I need to paste into
the first empty cell in column A.

Would you mind filling in the blanks here?:

Worksheets(rt).<the first empty cell in column a.Paste

thanks again,

Damien

Voila,
Worksheets(rt).range("A2").End(xlDown).Offset(1,0)
Regards
JY

"Damien McBain" wrote in message
...
Jean-Yves wrote:

In : For Each rt In Worksheets("Data").Range("A2",
Range("A2").End(xlDown))
Worksheets("Data").Range("A2" : Refer to A2 on Worksheets("Data")
Range("A2").End(xlDown) Refer to all cell in column A on the active
sheet,.
ie new wk RT

Therfor you have to repeat the sheet name :
Worksheets("Data").Range("A2",
Worksheets("Data").Range("A2").End(xlDown))
Regards
JY

Cheers JY, thanks to you that part of the code is working now. The bit I'm
having trouble with now is pasting into the new sheet. I need to paste
into
the first empty cell in column A.

Would you mind filling in the blanks here?:

Worksheets(rt).<the first empty cell in column a.Paste

thanks again,

Damien