Trouble sorting data by cell value

  • Thread starter Thread starter Shandy720
  • Start date Start date
S

Shandy720

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
 
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?
 
I now get the error message
Runtime error '1004'
'Intersect' of Object '_Global' faile
 
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
 
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


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
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
Back
Top