speed macro using array

hye all,

I have to do a project which required me to get specific data from a large excel doc. i use filtering function but it takes me about 20 minutes to come out with the result. I know we can do it using array, but i don't know how to do it. for example, i want a data from country : xxxx, account name : yyyy unit:zzzz revenue :1234, these data i have to paste it to another worksheet, now i'm using macro and it is very slow. what i'm not rite now is juz record a macro, that's why it is slow. can sumbody help me to speed up my process?i'm am more than happy to receive your help.

Maybe if we see the code we can. Record does create inefficient code.

You should look at analysing the data with pivot tables.

This is one example of the code. Can you help me to speed up the macro?

Sub php_1()

'GSM
Windows("Order PSM.xls").Activate
Sheets("Original").Select
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=16, Criteria1:="2008"
Selection.AutoFilter Field:=17, Criteria1:="1"
Selection.AutoFilter Field:=22, Criteria1:="<>tobeclosed", Operator:= _
xlAnd
Selection.AutoFilter Field:=2, Criteria1:="Philippines"
Selection.AutoFilter Field:=9, Criteria1:="GSMWD"
Columns("R:R").Select
Selection.Copy
Windows("Countries.xls").Activate
Sheets("php").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'WIMAX

Windows("Order PSM.xls").Activate
Sheets("Original").Select
Rows("1:1").Select
Selection.AutoFilter Field:=9, Criteria1:="WIMAX"
Columns("R:R").Select
Selection.Copy
Windows("Countries.xls").Activate
Sheets("php").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'WCDMA

Windows("Order PSM.xls").Activate
Sheets("Original").Select
Rows("1:1").Select
Selection.AutoFilter Field:=9, Criteria1:="WCDMAD"
Columns("R:R").Select
Selection.Copy
Windows("Countries.xls").Activate
Sheets("php").Select
Range("E4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'CDMA

Windows("Order PSM.xls").Activate
Sheets("Original").Select
Rows("1:1").Select
Selection.AutoFilter Field:=9, Criteria1:="CDMAD"
Columns("R:R").Select
Selection.Copy
Windows("Countries.xls").Activate
Sheets("php").Select
Range("F4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


End Sub



--pivot table?can u tell me how can pivot table can help me speed up the macro. tanx a lot for ur reply:friends:

This is untested as I have nothing to test it on, but see if this is quicker

[vba]

Sub php_1()

Application.ScreenUpdating = False

With Workbooks("Order PSM.xls")

With Sheets("Original")

With .Rows("1:1")

.AutoFilter Field:=16, Criteria1:="2008"
.AutoFilter Field:=17, Criteria1:="1"
.AutoFilter Field:=22, Criteria1:="<>tobeclosed"
.AutoFilter Field:=2, Criteria1:="Philippines"
End With

'GSM

.Rows("1:1").AutoFilter Field:=9, Criteria1:="GSMWD"
.Columns("R:R").Copy
Workbooks("Countries.xls").Worksheets("php").Range("A4").PasteSpecial Paste:=xlValues

'WIMAX

.Rows("1:1").AutoFilter Field:=9, Criteria1:="WIMAX"
.Columns("R:R").Copy
Workbooks("Countries.xls").Worksheets("php").Range("D4").PasteSpecial Paste:=xlValues

'WCDMA

.Rows("1:1").AutoFilter Field:=9, Criteria1:="WCDMAD"
.Columns("R:R").Copy
Workbooks("Countries.xls").Worksheets("php").Range("E4").PasteSpecial Paste:=xlValues


'CDMA

.Rows("1:1").AutoFilter Field:=9, Criteria1:="CDMAD"
.Columns("R:R").Copy
Workbooks("Countries.xls").Worksheets("php").Range("F4").PasteSpecial Paste:=xlValues
End With
End With

Application.ScreenUpdating = True

End Sub
[/vba]

what if I want to do it using array? for example array that will matches the country and account together with the revenue. for example, in a large worksheet, there are a lot of data in it. from this large worksheet i want the array to find specific info,for instance sri lanka as the country, tigo as the account and get the revenue and paste it in the other worksheet.then, i want the array to find the same country but different account n etc. I know i can do it using array, but I don't know the way. can u help me? tanx a lot!:thumb:thumb