Assigning a variable to a table

[Knochel]

Can a variable be assigned to a table based on the active sheet? I have a workbook with multiple tabs which contain unique tables in each tab (one table per tab). I have a macro that will clear the contents of a table, but currently, I have to use the specific name of the table I want to clear in the macro. I want to have a button (text box with assigned macro) on each tab that allows me to clear the contents of the table of the active sheet, but I do not know how to assign a variable to the table on the active sheet. I know I can have a separate macro referencing the each table, but I want the macro to adjust for the active sheet. Below is the code I'm currently using:

Sub Clear_Data_ChangeMGT()
'
' Clear data from ChangeMGT_tbl table
'

'
Range("ChangeMGT_tbl[[Task Order]:[Department Interdependency]]").ClearContents
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

 

[Smallman]

Hi Knochel

If all of your tables start in the same place on each sheet then something like this. If they don’t start in the same place maybe a point where they are all in like C11. So change A1 to C11.

Sub testo()
  [a1].CurrentRegion.Clear
End Sub

Take care

Smallman

 

[NARAYANK991]

Hi Knochel ,

Try this :

Sub Clear_Data_from_Table()
'
' Clear data from table on active sheet
'
With ActiveSheet.ListObjects(1).DataBodyRange
    .ClearContents
    With .Interior
          .Pattern = xlNone
          .TintAndShade = 0
          .PatternTintAndShade = 0
    End With
End With
End Sub

All the buttons can call this same procedure , since it works on the active sheet , and since you mention that there is only one table per sheet.

Narayan

 

[Smallman]

Using my concept with Narayan's this will do the same thing.

Sub ClearIt()
  ActiveSheet.ListObjects(1).DataBodyRange.Clear
End Sub

Take care

Smallman