jmac001
asked on
Reference by column name instead of an absolute number
Hi, I have a working macro that performs the following:
1. Selects all the fields in a table in the column "Functional Area" besides the function that was selected on the cover sheet (aka. Series_Name);
2. Deletes all those fields and their data in the table;
3. Then un-filters (shows) only the selected function
It is running fine when I refer to the column number of Initiative Summary tab (number 3). However, when I'm trying to reference by name instead of referring to absolute column numbers, the macro doesn't work.
o Instead of:
¿ Sheets("Initiative Summary").ListObjects("Ini tiativeTbl ").Range.A utoFilter Field:=3
o I try:
¿ Sheets("Initiative Summary").ListObjects("Ini tiativeTbl ").Range.A utoFilter Field:=.ListColumns("Funct ional Area").Index
Could you please help me figure out how to make it work?
Sub Macro3()
'
' Macro3 Macro
'
Dim Series_Name As String
Series_Name = Range("Cover!$C$8").Value
If Series_Name <> "All" Then
Sheets("Initiative Summary").ListObjects("Ini tiativeTbl ").Range.A utoFilter Field:=3
With Sheets("Initiative Summary").ListObjects("Ini tiativeTbl ")
.Range.AutoFilter Field:=.ListColumns("Funct ional Area").Index, Criteria1:=("<>" & Series_Name), Operator:=xlFilterValues
End With
Sheets("Initiative Summary").ListObjects("Ini tiativeTbl ").ListCol umns("Func tional Area").DataBodyRange.Speci alCells(xl CellTypeVi sible).Del ete
With Sheets("Initiative Summary").ListObjects("Ini tiativeTbl ")
.Range.AutoFilter Field:=3
End With
Else
Sheets("Initiative Summary").ListObjects("Ini tiativeTbl ").Range.A utoFilter Field:=3
End If
End Sub
1. Selects all the fields in a table in the column "Functional Area" besides the function that was selected on the cover sheet (aka. Series_Name);
2. Deletes all those fields and their data in the table;
3. Then un-filters (shows) only the selected function
It is running fine when I refer to the column number of Initiative Summary tab (number 3). However, when I'm trying to reference by name instead of referring to absolute column numbers, the macro doesn't work.
o Instead of:
¿ Sheets("Initiative Summary").ListObjects("Ini
o I try:
¿ Sheets("Initiative Summary").ListObjects("Ini
Could you please help me figure out how to make it work?
Sub Macro3()
'
' Macro3 Macro
'
Dim Series_Name As String
Series_Name = Range("Cover!$C$8").Value
If Series_Name <> "All" Then
Sheets("Initiative Summary").ListObjects("Ini
With Sheets("Initiative Summary").ListObjects("Ini
.Range.AutoFilter Field:=.ListColumns("Funct
End With
Sheets("Initiative Summary").ListObjects("Ini
With Sheets("Initiative Summary").ListObjects("Ini
.Range.AutoFilter Field:=3
End With
Else
Sheets("Initiative Summary").ListObjects("Ini
End If
End Sub
Post the file here, okay?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.