I have an Excel 2007 workbook that has a number of data tables fed from
Access. The tables contain financial data in a crosstab with the customer
name as the row, the month end date as the column and the sum of the monthly
amounts as the value.

The last column of each table is a sum for YTD 2009. Currently, I go
through each worksheet, update the formula to include the most current month
and sort the total decending. The sort is critical to properly rank the
customers for each worksheet. (I have tried the ranking function, but some
customers will have zero or negative purchases for the year, so I end up with
ties. I have tried many different tie-breaking methods, but all of them have
failed at one point or another).

Here is what I have so far:
Sub SortTables()
Dim ws As Worksheet

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

For Each ws In Worksheets

ws.ListObjects("Table_Trinity.accdb6912" _
).Sort.SortFields.Clear
ws.ListObjects("Table_Trinity.accdb6912" _
).Sort.SortFields.Add Key:=Range( _
"Table_Trinity.accdb6912[[#Headers],[SortColumn]]"),
SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortTextAsNumbers
With ws.ListObjects( _
"Table_Trinity.accdb6912").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Next ws

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

This works great for the first sheet that has table 6912 on it, but what I
don't know how to do is to replace the "Table_Trinity.accdb6912" with 'the
table on the current worksheet'.

Is there a way to do that?

Thanks!

PJ