Reputation: 674
Goal
A button that executes Autofilter on a table based on cell values.
Problem
When duplicating the sheet, the macro refers to the table on the original sheet.
Current Code
Sub Macro1()
ActiveSheet.ListObjects("Table33").Range.AutoFilter Field:=6, Criteria1:=">" & Range("K9").Value
End Sub
Is there a way to assign a table in a relative manner? The table always appears in the same cells, sheet by sheet.
Upvotes: 0
Views: 896
Reputation: 757
I have 3 examples for you, the first finds the table for the cell you specify. In this case you need to change the D6
in TableName = ActiveSheet.Range("D6").ListObject.Name
to a cell inside the table. After finding the table, it runs the filter on that table. All 3 examples will throw a message box if a table is not found, you can comment that out or delete it if you don't want it. You should be able to tie your button to any of the 3 and use it.
I found the code to find the table here and modified it to work with your provided code.
Sub RangeTable()
Dim TableName As String
Dim ActiveTable As ListObject
'Determine if ActiveCell is inside a Table
On Error GoTo NoTableSelected
TableName = ActiveSheet.Range("D6").ListObject.Name 'Change range to cell inside of table
Set ActiveTable = ActiveSheet.ListObjects(TableName)
On Error GoTo 0
'Do something with your table variable (ie Add a row to the bottom of the ActiveTable)
ActiveTable.Range.AutoFilter Field:=6, Criteria1:=">" & Range("K9").Value
Exit Sub
'Error Handling
NoTableSelected:
MsgBox "There is no Table currently selected!", vbCritical
End Sub
The code below will look at the cell you currently have selected, find the table associated with it and then run the filter using that table.
Sub ActiveTable()
Dim SelectedCell As Range
Dim TableName As String
Dim ActiveTable As ListObject
Set SelectedCell = ActiveCell
'Determine if ActiveCell is inside a Table
On Error GoTo NoTableSelected
TableName = SelectedCell.ListObject.Name
Set ActiveTable = ActiveSheet.ListObjects(TableName)
On Error GoTo 0
'Do something with your table variable (ie Add a row to the bottom of the ActiveTable)
ActiveTable.Range.AutoFilter Field:=6, Criteria1:=">" & Range("K9").Value
Exit Sub
'Error Handling
NoTableSelected:
MsgBox "There is no Table currently selected!", vbCritical
End Sub
Another alternative is the code below which just runs the filter on the first table found on the ActiveSheet so if you just have one table then this should work fine. With this one you don't need to select a cell inside the table before running it but if you have more than one table per sheet you may want to use the above code.
Sub SheetTable()
Dim TableName As String
Dim ActiveTable As ListObject
'Determine if ActiveCell is inside a Table
On Error GoTo NoTableSelected
TableName = ActiveSheet.ListObjects.Item(1)
Set ActiveTable = ActiveSheet.ListObjects(TableName)
On Error GoTo 0
'Do something with your table variable (ie Add a row to the bottom of the ActiveTable)
ActiveTable.Range.AutoFilter Field:=6, Criteria1:=">" & Range("K9").Value
Exit Sub
'Error Handling
NoTableSelected:
MsgBox "There is no Table currently selected!", vbCritical
End Sub
Upvotes: 2