speed macro using array

[lavenderial]

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.

[Bob Phillips]

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

You should look at analysing the data with pivot tables.

[lavenderial]

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

[Bob Phillips]

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]

[lavenderial]

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!