Trouble with Recorded Macro working in multiple worksheets

[gbrennan]

MS Excel 2010.

I attempted to record a macro to sort data within an active worksheet. I want the Macro to work in the active worksheet of the multiple worsheet workbook. Below is the macro as recorded. When I go to a worksheet where I did not physically record the macro, making it the "active worksheet", and attempt to run the macro, it goes back to the worksheet where I recorded the Macro and runs it only on that sheet (which does no good because my data is already sorted on that sheet from the last time that I ran the Macro). Does any one have a solution?

Sub Datasort()
'
' Datasort Macro
' Sorts Timesheet Data
'
' Keyboard Shortcut: Ctrl+s
'
ActiveCell.Offset(-19, -4).Range("A1:H34").Select
ActiveWorkbook.Worksheets("Tue 092011").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Tue 092011").Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 4).Range("A1:A34"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Tue 092011").Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 5).Range("A1:A34"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Tue 092011").Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 7).Range("A1:A34"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Tue 092011").Sort
.SetRange ActiveCell.Offset(-1, 0).Range("A1:H35")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveCell.Offset(15, 0).Range("A1").Select
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

Try replacing all instances of:

ActiveWorkbook.Worksheets("Tue 092011")

with:

ActiveSheet

 

[VoG]

Try this

Sub Datasort()
'
' Datasort Macro
' Sorts Timesheet Data
'
' Keyboard Shortcut: Ctrl+s
'
ActiveCell.Offset(-19, -4).Range("A1:H34").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 4).Range("A1:A34"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 5).Range("A1:A34"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 7).Range("A1:A34"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveSheet.Sort
    .SetRange ActiveCell.Offset(-1, 0).Range("A1:H35")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveCell.Offset(15, 0).Range("A1").Select
End Sub