I've had the unenviable chore to re-write Macro's to work with a legacy machine. They were created by Macro-Recording in Excel 2013 but they wouldn't run on our heavy machinery PC's out in our shop. Upgrading them to Office 2013 is currently not an option.
Here is the original Macro:
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"G2:G13"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
With ActiveWorkbook.Worksheets("26 GA SP & GV").Sort
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
Through some research and assistance, it was determined to re-write it to this:
Dim Ws As Worksheet
Set Ws = Worksheets("26 GA SP & GV")
Application.ScreenUpdating = False
Ws.Range("D2:J13").Sort Key1:=Ws.Range("G2"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Application.ScreenUpdating = True
So, all of the Macro's on the first weeksheet called 26 GA SP & GV work just fine in Excel 2003. However, there are 3 more worksheets and simply using this format for them isn't working. The kicker: all of the remaining macros are all stored in one module. The ones for the first sheet had individual modules. So I am thinking this is where the problem lies but sure how to fix it. If I try to run these re-written Macros for the other worksheets, I get this error:
RUN TIME ERROR 1004 - SORT METHOD OF RANGE CLASS FAILED.
I'm thinking it might have something to do with them all being in one module but not sure. Any suggestions?