Autofilter by column name not column number

[joey1984]

I have the AutoFilter Field:=4 piece of code but what if the column number changes?

How can i assigned AutoFilter field to be the actual column name. The column name is Current State and have declared it in the below code as string --> strSearch4 = "Current State"

Should i assign strSearch4 to AutoFilter Field? If so how?

Sub CommercialView() ' ' CommercialView Macro '

' Dim wrkbk, sourceBk As Workbook Set sourceBk = Application.ActiveWorkbook 'Clear Filter for all Columns START With ActiveSheet If .AutoFilterMode Then If .FilterMode Then .ShowAllData End If Else If .FilterMode Then .ShowAllData End If End If End With 'Clear Filter from all Columns END

'Copy the required columns and add them to the destination spreadsheet START
Workbooks.Add
Set wrkbk = Application.ActiveWorkbook
sourceBk.Activate
wrkbk.Activate
sourceBk.Activate

Dim aCell1, aCell2, aCell3, aCell4, aCell5, aCell6, aCell7, aCell8, aCell9, aCell10, aCell11, aCell12 As Range
Dim strSearch1, strSearch2, strSearch3, strSearch4, strSearch5, strSearch6, strSearch7, strSearch8, strSearch9, strSearch10, strSearch11, strSearch12 As String

strSearch1 = "Change Request Description"
strSearch2 = "PCR No."
strSearch3 = "Accn. ID"
strSearch4 = "Current State"
strSearch5 = "Approved Date"
strSearch6 = "Project"
strSearch7 = "Planned Commencement Date"
strSearch8 = "Notes"
strSearch9 = "Total Price (IIA, DIA, Execution ($)"
strSearch10 = "Price Calculator Status"
strSearch11 = "OM Entry"
strSearch12 = "CVP Ref. No."

Set aCell1 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch1, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Set aCell2 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch2, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Set aCell3 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch3, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Set aCell4 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch4, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Set aCell5 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch5, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Set aCell6 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch6, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Set aCell7 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch7, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Set aCell8 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch8, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Set aCell9 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch9, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Set aCell10 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch10, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Set aCell11 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch11, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Set aCell12 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch12, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

'~~> Do the copying here

Sheets("3. PMO Internal View").Range(Sheets("3. PMO Internal View").Columns(aCell1.Column).Address & "," _
& Sheets("3. PMO Internal View").Columns(aCell2.Column).Address & "," _
& Sheets("3. PMO Internal View").Columns(aCell3.Column).Address & "," _
& Sheets("3. PMO Internal View").Columns(aCell4.Column).Address & "," _
& Sheets("3. PMO Internal View").Columns(aCell5.Column).Address & "," _
& Sheets("3. PMO Internal View").Columns(aCell6.Column).Address & "," _
& Sheets("3. PMO Internal View").Columns(aCell7.Column).Address & "," _
& Sheets("3. PMO Internal View").Columns(aCell8.Column).Address & "," _
& Sheets("3. PMO Internal View").Columns(aCell9.Column).Address & "," _
& Sheets("3. PMO Internal View").Columns(aCell10.Column).Address & "," _
& Sheets("3. PMO Internal View").Columns(aCell11.Column).Address & "," _
& Sheets("3. PMO Internal View").Columns(aCell12.Column).Address).Copy

'Range("A1,B1,C1,D1,E1,G1,H1,I1,R1,V1,W1,X1").EntireColumn.Select
'Selection.Copy

Range("A2").Select
wrkbk.Activate
ActiveSheet.Paste
Selection.AutoFilter
'Copy the required columns and add them to the destination spreadsheet END

'To remove data validation START
Cells.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'To remove data validation END

wrkbk.Activate
wrkbk.Sheets("Sheet1").Select
ActiveSheet.Range("$A$1:$L$4000").AutoFilter Field:=10, Criteria1:= _
"Completed - Requires Review from Pricing"


'Copy the Status Definitions tab to the new worksheet START
sourceBk.Sheets("2. Status Definitions").Copy _
after:=ActiveWorkbook.Sheets("Sheet1")
'Copy the Status Definitions tab to the new worksheet END

wrkbk.Sheets("Sheet1").Select
Range("A5").Select


ActiveWorkbook.SaveAs ("C:\Users\joseph.jaajaa\Desktop\DOD\Change Status Request Report\Commercial View\Internal Change Status Request Report - Commercial View - " & Format(Now, "yyyy-mm-dd"))
ActiveWorkbook.Close

'Return back to Overall CR Tracker and filter out Approved and Cancelled CRs START
ActiveSheet.Range("$A$1:$AB$65").**AutoFilter Field:=4**, Criteria1:=Array( _
"Detailed Impact Assessment", "Draft – Yet to be Tabled at CCCM", _
"Initial Impact Assessment", "New", "On Hold", "Pending Approval - Execution", _
"Pending Approval - IIA"), Operator:=xlFilterValues
'Return back to Overall CR Tracker and filter out Approved and Cancelled CRs END
End Sub

 

[hippiehacker]

Hi

Determine which column contains the Header value and therefore identify what number to send to the autofilter command:

Dim i as integer, rngData as Range
Set rngData = Range("A1").CurrentRegion
i = Application.Worksheetfunction.Match("Names",Range("A1:AZ1"),0)

rngData.AutoFilter Field:=i, Criteria1:=">=2005", Operator:=xlAnd, Criteria2:="<=2006"

Is a suitable code fragment. Simply amend as required.

Richard

EDIT: I should mention this code assumes your data range starts in A1 (ie headers in row 1).

see from Richard