I suspect that you can change...
ActiveSheet.ListObjects("Table1")
To...
ActiveSheet.ListObjects(1)
and have it work.
However...
Key:=Range("Table1[[#All],[Name]]") ...will probably take some work???
I am not the one to help with that.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - check out "Special Sort")
"eleinia"
wrote in message
Hi - thanks for the replies! Yes, I am using XL2007.
I replaced ActiveWorkbook.Worksheets("Sheet1") with ActiveSheet as
suggested, and the macro still worked on the original worksheet.
However it still references Table 1, and the name of the table will
change incrementally each time the worksheet is copied, so it did not
work on the new worksheet.
Any ideas on the correct way to reference that:
The new macro is as follows:
Sub Macro19()'
' Macro19 Macro
'
ActiveSheet.ListObjects("Table1").Sort.SortFields. Clear
ActiveSheet.ListObjects("Table1").Sort.SortFields. Add _
Key:=Range("Table1[[#All],[Name]]"), SortOn:=xlSortOnValues,
Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveSheet.ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Cheers
Sarah