Prev: user to input date as "yyyy" in cell
Next: copy paste fail
From: Mr. IT Mr. on 24 Feb 2010 04:53
Greetings!

I am working in a good IT company under the quality management team. May I
ask how can I automatically sort an array of records using macros based on
categories and heirarchy of scores? Then generate a separate sheet for it as
well?

Here is a simple example of a record array found in sheet1 named as "DATA":

A B C
PARTICIPANT CATEGORY POINTS
1 Malaysia W 87,000
2 Indonesia X 12,000
3 Singapore Y 98,000
4 Thailand Z 15,000
5 Hong-Kong Y 58,000
6 Japan W 108,000
7 Russia X 33,000
8 China Z 72,000

I simply need to automatically generate two more sheets: Sheet 2 as
"GroupsWX" which will contain all entries having categories 'W' and 'X' and
Sheet 3 as "GroupsYZ" which will contain all entries having categories 'Y'
and 'Z'.

These two computer-generated sheets will display Columns A and C in
descending order; without having the need to separate 'W' from 'X' or 'Y'
from 'Z'.

Thank you very much and God Bless to you, your family, and your company =)\

Regards, Mr. IT
From: marcus on 24 Feb 2010 07:01
Hello Mr IT

This should do what you want, in terms of splitting the data into two
sheets. The sort assumes you start putting data in Range A1 and the
data is presented in a structured manner.

Take care

Marcus


Sub CopytoSheet()
Dim RngCell As Range
Dim MyList() As Variant
Dim res As Variant
Dim lw As Long
Dim X As Range
Dim ws As Worksheet

lw = Range("B" & Rows.Count).End(xlUp).Row
MyList() = Array("W", "X")

Set X = Range("B2:B" & lw)
For Each RngCell In X
res = Application.Match(RngCell.Value, MyList, 0)
If IsError(res) Then
RngCell.EntireRow.Copy Sheets("Sheet3"). _
Range("A65536").End(xlUp).Offset(1, 0)
Else
RngCell.EntireRow.Copy Sheets("Sheet2"). _
Range("A65536").End(xlUp).Offset(1, 0)
End If
Next RngCell

'Sort the ranges
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "Sheet2" Or ws.Name = "Sheet3" Then

With ws.Range("A1").CurrentRegion
.Sort Key1:=.Cells(2, "A"), Order1:=xlDescending,
Header:=xlYes
End With

End If
Next

End Sub


 | 
Pages: 1
Prev: user to input date as "yyyy" in cell
Next: copy paste fail