Utilize Macro On New Worksheet

[bmart]

I recorded a very simple macro and added a 'button' in order to run it. On the original worksheet, everything functions properly. However, I will be generating a new worksheet and report every week, so I'd like to be able to add the macro to future worksheets. I thought that changing the worksheet name to 'ActiveSheet' would solve the problem. However, I believe I am now running into an issue with the Table Name 'ListObjects' in VBA.

On the original worksheet, the table was named 'Table 14'. However, each subsequent worksheet that I create will have a different table name (Excel requirement).

Does anyone have any recommendation on changes to the following code that will detach the macro from a specific table (object)?

Thanks in advance!

Sub SortCash()
'
' SortCash Macro
'


'
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.ActiveSheet.ListObjects("Table14").Sort. _
SortFields.Clear
ActiveWorkbook.ActiveSheet.ListObjects("Table14").Sort. _
SortFields.Add Key:=Range("F2"), SortOn:=xlSortOnValues, Order:= _
xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.ListObjects("Table14"). _
Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Roderick_E]

If there is just one table on the new workbook, you could loop to get that table name

 

[bmart]

Since I will be running the report weekly, I will keep multiple weeks (ie multiple worksheets) in the same workbook. Thus there will be several tables in the workbook, which is where the problem presents itself.

For example: On worksheet 2, the code would need to read 'ActiveWorkbook.ActiveSheet.ListObjects("Table5").Sort. _SortFields.Clear.....'