I don't have xl2007 but the change shown below should allow you to sort on any sheet in xl2007.  
The change will not allow you to sort in prior versions of Excel as
the recorder is using terms that are not recognized by earlier versions.
My recommendation is to just replace the xl2007 recorded code with the XL2003 version.
'--
Replace (all instances of):
ActiveWorkbook.Worksheets("tab name")

With"
ActiveSheet
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

<chunt@vvsii.com>
wrote in message
Hello,

I'm trying to figure out why there is a difference like this between
excel 2003 and 2007.  Perhaps there's a setting somewhere that I need
to change.

When I record a macro in 2003 to the personal workbook, that macro
then works for me in every document that I open thereafter and try to
run it in.  In 2007 it seems to associate my selecting with the
particular file that I record the macro in, so that when I go to run
it later on a different report, but I want to do the same changes, it
comes up with an error because it is searching for the selection in
the other document name.  Hopefully I've explained it alright.

I'm posting bellow first the 2003 macro that works in all subsequent
documents first and then a similar macro from 2007.  If someone could
please help me I'd be very grateful.

Thanks,

2003:

Sub reports()
'
' reports Macro
' Macro recorded 1/3/2008 by xxxx
'
' Keyboard Shortcut: Ctrl+z
'
   Cells.Select
   Cells.EntireColumn.AutoFit
   Selection.Sort Key1:=Range("D1"), Order1:=xlAscending,
Header:=xlGuess, _
       OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
       DataOption1:=xlSortNormal
   Columns("E:L").Select
   Selection.Delete Shift:=xlToLeft
   Columns("C:C").Select
   With Selection
       .HorizontalAlignment = xlCenter
       .VerticalAlignment = xlBottom
       .WrapText = False
       .Orientation = 0
       .AddIndent = False
       .IndentLevel = 0
       .ShrinkToFit = False
       .ReadingOrder = xlContext
       .MergeCells = False
   End With
   Selection.ColumnWidth = 17.29
   Columns("B:B").ColumnWidth = 6.43
   Columns("A:A").ColumnWidth = 11.43
   Columns("A:B").Select
   With Selection
       .HorizontalAlignment = xlCenter
       .VerticalAlignment = xlBottom
       .WrapText = False
       .Orientation = 0
       .AddIndent = False
       .IndentLevel = 0
       .ShrinkToFit = False
       .ReadingOrder = xlContext
       .MergeCells = False
   End With
   Range("A1").Select
End Sub

2007

Sub test()
'
' test Macro
' test
'
' Keyboard Shortcut: Ctrl+t
'
   Cells.Select
   Cells.EntireColumn.AutoFit
   Range("D1").Select
   ActiveWorkbook.Worksheets("*****file name or tab name not sure
which is is*****").Sort.SortFields. _
       Clear
   ActiveWorkbook.Worksheets("*****file name or tab name not sure
which is is*****").Sort.SortFields. _
       Add Key:=Range("D1:D372"), SortOn:=xlSortOnValues,
Order:=xlAscending, _
       DataOption:=xlSortNormal
   With ActiveWorkbook.Worksheets("*****file name or tab name not
sure which is is*****").Sort
       .SetRange Range("A1:E372")
       .Header = xlGuess
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
   End With
   Columns("E:L").Select
   Selection.Delete Shift:=xlToLeft
   Columns("C:C").Select
   With Selection
       .HorizontalAlignment = xlCenter
       .VerticalAlignment = xlBottom
       .WrapText = False
       .Orientation = 0
       .AddIndent = False
       .IndentLevel = 0
       .ShrinkToFit = False
       .ReadingOrder = xlContext
       .MergeCells = False
   End With
   Selection.ColumnWidth = 17.29
   Columns("B:B").Select
   Selection.ColumnWidth = 15.14
   Columns("A:A").Select
   Selection.ColumnWidth = 16
   Columns("A:B").Select
   With Selection
       .HorizontalAlignment = xlCenter
       .VerticalAlignment = xlBottom
       .WrapText = False
       .Orientation = 0
       .AddIndent = False
       .IndentLevel = 0
       .ShrinkToFit = False
       .ReadingOrder = xlContext
       .MergeCells = False
   End With
   Range("A1").Select
End Sub