VBA Macro to Perform Functions

[Adrianga]

Hi I need to create a Macro that will take a large data set and perform a series of Filters.. To Ultimately Select the remaining Data information. Below is the set of steps:

Filter
1.Column (GX)- ALL_Status, Filter Out 'Completed'
2.Column (DW)- Low Util PON, Low Filter Out 'Blanks'
3.Column (EX) - TLA, Filter out ALL future dates greater (>) than today.

Copy/Transpose
4.Capture in File the following columns with Data reflecting:
-Groomer Id (AD)
-Plan Tracking Code(E)
-Ckt Type (M)
-Internal CKt Id1 (R)
-Low Util Pon (DW) TLA (EX)

5.Lastly I would like to transpose that information into a seperate sheet or file if thats possible (OPTIMA_TLA)
6. Then Sort by Groomer ID - A-Z

Any Help Much Appreciated!!

[Richard Buttrey]

Sounds like you need an Advanced Data Filter to extract the relevant records to a second sheet.

Have you tried setting up the requisite criteria range and output range and then capturing a macro with the macro recorder.

Do it manually first before you try recording a macro since my experience is that these never work as you expect the first time around.
The recorded macro will then want some tidying up but when you've done this upload the workbook and the code you've captured and no doubt we can tidy it up and generalise the code.

[Adrianga]

So when I perform this using the recorder and Trim out what I believe wasnt necessary here what is left:

1.One concern I have is the ranges it has listed wont always be the same due to raw data file changing.
2.Also when I try and run it after I get a error for the first section
3.Also the date range for the will always be > than Today() vs me manually deselecting dates.( Not sure how to write this)

Sub TLAGRAB()
'
' TLAGRAB Macro
'

'
ActiveSheet.Range("$A$1:$IA$20115").AutoFilter Field:=206, Criteria1:=Array _
("ASR", "ASSIGNED", "DISCO ASR", "DISCO PEND", "FIRM", "FOC", "FOC,MUX", "INS ASR", _
"INS FIRM", "INS FOC", "INS,FOC,MUX", "IO ASR", "IO FIRM", "IO FOC", "IO,FOC,MUX", _
"PRE-AUDIT"), Operator:=xlFilterValues

ActiveSheet.Range("$A$1:$IA$20115").AutoFilter Field:=127, Criteria1:="<>"

ActiveSheet.Range("$A$1:$IA$20115").AutoFilter Field:=154, Criteria1:=Array _
("05/10/16", "05/11/16", "05/12/16", "5/13/16", "5/16/16"), Operator:= _
xlFilterValues

Range("AD988:AD18763").Select
Selection.Copy
Sheets("OPTIMA_TLA").Select
ActiveSheet.Paste
Sheets("Optima Analysis").Select

Range("E988:E18763").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("OPTIMA_TLA").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("Optima Analysis").Select

Range("M988:M18763").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("OPTIMA_TLA").Select
Range("C2").Select
ActiveSheet.Paste
Sheets("Optima Analysis").Select

Range("R988:R18763").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("OPTIMA_TLA").Select
Range("D2").Select
ActiveSheet.Paste
Sheets("Optima Analysis").Select

Range("DW988:DW18763").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("OPTIMA_TLA").Select
Range("E2").Select
ActiveSheet.Paste
Sheets("Optima Analysis").Select

Range("EX988:EX18763").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("OPTIMA_TLA").Select
Range("F2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("OPTIMA_TLA").ListObjects("Table2").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("OPTIMA_TLA").ListObjects("Table2").Sort.SortFields. _
Add Key:=Range("Table2[[#All],[Groomer Id]]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("OPTIMA_TLA").ListObjects("Table2").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub