Hi,

I am having trouble writing a macro. I need to search the data i
'Sheet2' and sort the data by value into separate worksheets. So i
column A if the value of a cell is 9 the data will be pasted int
Worksheet'9-10', if the value of a cell is 10, the data will be paste
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,
Andre

--
Shandy72
-----------------------------------------------------------------------
Shandy720's Profile: http://www.excelforum.com/member.php...fo&userid=2623
View this thread: http://www.excelforum.com/showthread.php?threadid=39526

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' faile

--
Shandy72
-----------------------------------------------------------------------
Shandy720's Profile: http://www.excelforum.com/member.php...fo&userid=2623
View this thread: http://www.excelforum.com/showthread.php?threadid=39526

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

Try looking here at Ron de Bruin's site:

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

--
Regards,
Tom Ogilvy


"Shandy720" wrote
in message ...

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
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
__________________________