Trouble sorting data by cell value

Hi,

I am having trouble writing a macro. I need to search the data in 'Sheet2' and sort the data by value into separate worksheets. So in column A if the value of a cell is 9 the data will be pasted into Worksheet'9-10', if the value of a cell is 10, the data will be pasted into Worksheet'10-11' and so on...

This is the macro i have so far which will not work;
Sub STEP3()
Dim rng As Range, cell As Range, sel As Range
Set rng = Intersect(Range("A:A"), Sheets("Sheet2").UsedRange)
For Each cell In rng
If (cell.Value) = "9" Then
sel.EntireRow.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("9-10").Select
Range("A3").Select
ActiveSheet.Paste
If (cell.Value) = "10" Then
sel.EntireRow.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("10-11").Select
Range("A3").Select
ActiveSheet.Paste
If sel Is Nothing Then
Set sel = cell
Else: Set sel = Union(sel, cell)
On Error Resume Next
End If
End If
End If
Next
End Sub

Please can you help, Many thanks,
Andrew

Can you explain what is happening - where it is going wrong? Do you get an
error message? If not, what is different from the way the macro behaves and
the way you want it to behave?
--
- K Dales


"Shandy720" wrote:

>
> Hi,
>
> I am having trouble writing a macro. I need to search the data in
> 'Sheet2' and sort the data by value into separate worksheets. So in
> column A if the value of a cell is 9 the data will be pasted into
> Worksheet'9-10', if the value of a cell is 10, the data will be pasted
> into Worksheet'10-11' and so on...
>
> This is the macro i have so far which will not work;
> Sub STEP3()
> Dim rng As Range, cell As Range, sel As Range
> Set rng = Intersect(Range("A:A"), Sheets("Sheet2").UsedRange)
> For Each cell In rng
> If (cell.Value) = "9" Then
> sel.EntireRow.Select
> Application.CutCopyMode = False
> Selection.Copy
> Sheets("9-10").Select
> Range("A3").Select
> ActiveSheet.Paste
> If (cell.Value) = "10" Then
> sel.EntireRow.Select
> Application.CutCopyMode = False
> Selection.Copy
> Sheets("10-11").Select
> Range("A3").Select
> ActiveSheet.Paste
> If sel Is Nothing Then
> Set sel = cell
> Else: Set sel = Union(sel, cell)
> On Error Resume Next
> End If
> End If
> End If
> Next
> End Sub
>
> Please can you help, Many thanks,
> Andrew
>
>
> --
> Shandy720
> ------------------------------------------------------------------------
> Shandy720's Profile: http://www.excelforum.com/member.php...o&userid=26230
> View this thread: http://www.excelforum.com/showthread...hreadid=395266
>
>

I now get the error message
Runtime error '1004'
'Intersect' of Object '_Global' failed

I have a sheet of data in five columns which i need to organise into different worksheets. So i need a macro to check all cellvalues in ColumnA (which range from 9-20) and copy the entire row into a separate worksheet for each value.
In the ned i want a worksheet with all the rows which have '9' in ColumnA, a worksheet which has all the rows with '10' in ColumnA and so on.

Any help would be great, thanks a lot,
Andrew

Try looking here at Ron de Bruin's site:

http://www.rondebruin.nl/copy5.htm

--
Regards,
Tom Ogilvy


"Shandy720" <Shandy720.1tnk2m_1123851975.3508@excelforum-nospam.com> wrote
in message news:Shandy720.1tnk2m_1123851975.3508@excelforum-nospam.com...
>
> I have a sheet of data in five columns which i need to organise into
> different worksheets. So i need a macro to check all cellvalues in
> ColumnA (which range from 9-20) and copy the entire row into a separate
> worksheet for each value.
> In the ned i want a worksheet with all the rows which have '9' in
> ColumnA, a worksheet which has all the rows with '10' in ColumnA and so
> on.
>
> Any help would be great, thanks a lot,
> Andrew
>
>
> --
> Shandy720
> ------------------------------------------------------------------------
> Shandy720's Profile:
http://www.excelforum.com/member.php...o&userid=26230
> View this thread: http://www.excelforum.com/showthread...hreadid=395266
>

It sounds as if you need to filter the database. Either in place or by
extracting records according to a varying criteria and listing them
elsewhere.

Try recording a [Data Filter Advanced Filter] macro, see what the
result is and modify accordingly. Adding a loop perhaps to the macro
to modify the selection criteria.

Rgds


On Fri, 12 Aug 2005 07:52:43 -0500, Shandy720
<Shandy720.1tnk2m_1123851975.3508@excelforum-nospam.com> wrote:

>
>I have a sheet of data in five columns which i need to organise into
>different worksheets. So i need a macro to check all cellvalues in
>ColumnA (which range from 9-20) and copy the entire row into a separate
>worksheet for each value.
>In the ned i want a worksheet with all the rows which have '9' in
>ColumnA, a worksheet which has all the rows with '10' in ColumnA and so
>on.
>
>Any help would be great, thanks a lot,
>Andrew

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________