CODEWITHSUNDEEP
vbaexcel
Robert Tan
Robert Tan

Reputation: 674

Using one macro on different tables, independently

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

Answers (1)

tjb1
tjb1

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

Related Questions