How to set table name as a variable

Excel_ZM

New Member
Joined
Aug 16, 2016
Messages
24
I have many tables and I have to run VBA code for each table because the tables have different names. Is there a way to set the table name as a variable?

Here is my code.
Sub filter_all_tables_by_date()

x = Worksheets.Count

On Error Resume Next
For i = 1 To x
Sheets(i).Select
Range("A1").Select
ActiveSheet.ShowAllData

ActiveSheet.ListObjects("I-HAVE-TOO-MANY-TABLES-THIS-NEEDS-TO-BE-A-VARIABLE").Range.AutoFilter Field:=3, Criteria1:=1, _
Operator:=11, Criteria2:=0, SubField:=0

Next i
On Error GoTo 0
Sheets(1).Select

End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Never mind. I figured it out. I used this.
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=3, Criteria1:=1, _
Operator:=11, Criteria2:=0, SubField:=0
 
Upvote 0
A few points...

1) The way in which you are using your error handling could be hiding errors, other than the ones you anticipate.

2) If you have a Chart sheet (or some sheet other than a worksheet) at the beginning or middle of your workbook, not all of your worksheets would get processed. And, you would get an error with Range("A1").Select, which would be hidden by your error handling.

3) If your table has not been filtered, you'll get an error with ActiveSheet.ShowAllData, which also would be hidden by your error handling.

4) Usually, there's no need to do any type of selecting. And, in your case, this would apply.

Therefore, your macro could be re-written as follows. Note that it assumes that the workbook running the code contains the relevant worksheets. However, if you want the code to run based on the active workbook, replace...

VBA Code:
For Each ws In ThisWorkbook.Worksheets

with

VBA Code:
For Each ws In ActiveWorkbook.Worksheets

Here's the code...

VBA Code:
Sub filter_all_tables_by_date()

    Dim ws As Worksheet
    Dim lo As ListObject
   
    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next
        Set lo = ws.ListObjects(1)
        On Error GoTo 0
        If Not lo Is Nothing Then
            With lo
                If .ShowAutoFilter Then
                    With .AutoFilter
                        If .FilterMode Then .ShowAllData
                    End With
                End If
                .Range.AutoFilter Field:=3, Criteria1:=1, Operator:=11, Criteria2:=0, SubField:=0
            End With
            Set lo = Nothing
        End If
    Next ws

End Sub

Hope this helps!
 
Last edited:
Upvote 0
A few points...

1) The way in which you are using your error handling could be hiding errors, other than the ones you anticipate.

2) If you have a Chart sheet (or some sheet other than a worksheet) at the beginning or middle of your workbook, not all of your worksheets would get processed. And, you would get an error with Range("A1").Select, which would be hidden by your error handling.

3) If your table has not been filtered, you'll get an error with ActiveSheet.ShowAllData, which also would be hidden by your error handling.

4) Usually, there's no need to do any type of selecting. And, in your case, this would apply.

Therefore, your macro could be re-written as follows. Note that it assumes that the workbook running the code contains the relevant worksheets. However, if you want the code to run based on the active workbook, replace...

VBA Code:
For Each ws In ThisWorkbook.Worksheets

with

VBA Code:
For Each ws In ActiveWorkbook.Worksheets

Here's the code...

VBA Code:
Sub filter_all_tables_by_date()

    Dim ws As Worksheet
    Dim lo As ListObject
 
    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next
        Set lo = ws.ListObjects(1)
        On Error GoTo 0
        If Not lo Is Nothing Then
            With lo
                If .ShowAutoFilter Then
                    With .AutoFilter
                        If .FilterMode Then .ShowAllData
                    End With
                End If
                .Range.AutoFilter Field:=3, Criteria1:=1, Operator:=11, Criteria2:=0, SubField:=0
            End With
            Set lo = Nothing
        End If
    Next ws

End Sub

Hope this helps!
Thanks! That's a lot of useful tips! Especially point number 4. I didn't realize I did not have to select the sheets.

Just out of curiosity, the code targets the column C to filter. This is the "Date" field. What if the "Date" field was 4th or 5th column? Is there a way to make the code "smarter" so that it looks for the field that is named "Date" then filters that field? Thanks again.
 
Upvote 0
In that case, try the following instead...

VBA Code:
Sub filter_all_tables_by_date()

    Dim ws As Worksheet
    Dim lo As ListObject
    Dim fnd As Range
    
    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next
        Set lo = ws.ListObjects(1)
        On Error GoTo 0
        If Not lo Is Nothing Then
            With lo
                If .ShowAutoFilter Then
                    With .AutoFilter
                        If .FilterMode Then .ShowAllData
                    End With
                End If
                With .Range
                    Set fnd = .Rows(1).Find(what:="Date", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
                    If Not fnd Is Nothing Then
                        .AutoFilter Field:=fnd.Column, Criteria1:=1, Operator:=11, Criteria2:=0, SubField:=0
                    End If
                End With
            End With
            Set lo = Nothing
        End If
    Next ws

End Sub

Hope this helps!
 
Upvote 0
In that case, try the following instead...

VBA Code:
Sub filter_all_tables_by_date()

    Dim ws As Worksheet
    Dim lo As ListObject
    Dim fnd As Range
   
    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next
        Set lo = ws.ListObjects(1)
        On Error GoTo 0
        If Not lo Is Nothing Then
            With lo
                If .ShowAutoFilter Then
                    With .AutoFilter
                        If .FilterMode Then .ShowAllData
                    End With
                End If
                With .Range
                    Set fnd = .Rows(1).Find(what:="Date", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
                    If Not fnd Is Nothing Then
                        .AutoFilter Field:=fnd.Column, Criteria1:=1, Operator:=11, Criteria2:=0, SubField:=0
                    End If
                End With
            End With
            Set lo = Nothing
        End If
    Next ws

End Sub

Hope this helps!
That runs perfectly! Thank you so much!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top