Hi!
Excel 2007
The following first section of my code works perfectly. It provides me with a unique list of names from column U on the "Current Prog Accts" worksheet and pastes it formatted the way I wish A-Z on Sheet1. I would like the macro to continue on in section 2 using the same unique names derived from the AdvancedFilter, but add a criteria that the text "AME" must also be in column G for that unique name to still be included and then paste the results to Sheet2. However, I have no idea if I should further refine my criteria using AdvancedFilter, AutoFilter, or ...?
I realize section 2 has pretty much identical code as section1. I just don't know how to make it incorporate the additional level of criteria from column G that I need.
Note: if someone can help me with this 2nd section, I can extrapolate this for two more sheets with "EMEA" and "APJ" as the specific criteria for inclusion on their respective output sheets. Yes, I do need all of this in a single macro.
Thanks for any and all replies.
-doug
Excel 2007
The following first section of my code works perfectly. It provides me with a unique list of names from column U on the "Current Prog Accts" worksheet and pastes it formatted the way I wish A-Z on Sheet1. I would like the macro to continue on in section 2 using the same unique names derived from the AdvancedFilter, but add a criteria that the text "AME" must also be in column G for that unique name to still be included and then paste the results to Sheet2. However, I have no idea if I should further refine my criteria using AdvancedFilter, AutoFilter, or ...?
I realize section 2 has pretty much identical code as section1. I just don't know how to make it incorporate the additional level of criteria from column G that I need.
Note: if someone can help me with this 2nd section, I can extrapolate this for two more sheets with "EMEA" and "APJ" as the specific criteria for inclusion on their respective output sheets. Yes, I do need all of this in a single macro.
Code:
Sub NamesFilter()
: Rem Section 1 - Display unique names of sponsors on Sheet1
Sheets("Sheet1").Activate
Range("A3:A965").ClearContents
With Sheets("Current Prog Accts").Range("$U$2:$U$965")
.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Sheet1!A3"), Unique:=True
End With
Sheets("Sheet1").Select
Range("A3:A965").Select
With Range("A2:A965")
.Borders.LineStyle = xlNone
.Font.Color = 2
Selection.Interior.ColorIndex = xlNone
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2:A965"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
: Rem Section 2 - Display unique names of sponsors only from 'AME' on Sheet2
Sheets("Sheet2").Activate
Range("A3:A965").ClearContents
With Sheets("Current Prog Accts").Range("$U$2:$U$965")
.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Sheet2!A3"), Unique:=True
End With
Sheets("Sheet2").Select
Range("A3:A965").Select
With Range("A2:A965")
.Borders.LineStyle = xlNone
.Font.Color = 2
Selection.Interior.ColorIndex = xlNone
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2:A965"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub
Thanks for any and all replies.
-doug