Rewriting VBA Macros from Excel 2013 to Excel 2003 - Works One One Sheet Only.



  • 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:

    Sub Macro1()
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range( _
    "G2:G13"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("26 GA SP & GV").Sort
    .SetRange Range("D2:J13")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    End Sub

    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?

    Thanks...



  • @garak0410 said in Rewriting VBA Macros from Excel 2013 to Excel 2003 - Works One One Sheet Only.:

    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:

    Sub Macro1()
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range( _
    "G2:G13"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("26 GA SP & GV").Sort
    .SetRange Range("D2:J13")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    End Sub

    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?

    Thanks...

    The way your new Macro is written, it looks like it's only meant to work on one worksheet.

    Can you loop through the worksheets and run the Macro on each one?



  • @dafyre said in Rewriting VBA Macros from Excel 2013 to Excel 2003 - Works One One Sheet Only.:

    @garak0410 said in Rewriting VBA Macros from Excel 2013 to Excel 2003 - Works One One Sheet Only.:

    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:

    Sub Macro1()
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range( _
    "G2:G13"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("26 GA SP & GV").Sort
    .SetRange Range("D2:J13")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    End Sub

    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?

    Thanks...

    The way your new Macro is written, it looks like it's only meant to work on one worksheet.

    Can you loop through the worksheets and run the Macro on each one?

    Well, I've changed the Worksheet name on the other sheets to this:
    Set Ws = Worksheets("26 GA SP & GV")
    Set Ws = Worksheets("26 GA KY")
    Set Ws = Worksheets("24 GA KY")

    And the code always dies here:
    (different ranges of course per macro)
    Ws.Range("D2:J9").Sort Key1:=Ws.Range("G2"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

    I did pull one of the Macros out and put them in a module alone and still crashes.

    I think I am "oh so close" on this but with my limited VBA knowledge, can't say.



  • @garak0410 said in Rewriting VBA Macros from Excel 2013 to Excel 2003 - Works One One Sheet Only.:

    @dafyre said in Rewriting VBA Macros from Excel 2013 to Excel 2003 - Works One One Sheet Only.:

    @garak0410 said in Rewriting VBA Macros from Excel 2013 to Excel 2003 - Works One One Sheet Only.:

    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:

    Sub Macro1()
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range( _
    "G2:G13"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("26 GA SP & GV").Sort
    .SetRange Range("D2:J13")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    End Sub

    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?

    Thanks...

    The way your new Macro is written, it looks like it's only meant to work on one worksheet.

    Can you loop through the worksheets and run the Macro on each one?

    Well, I've changed the Worksheet name on the other sheets to this:
    Set Ws = Worksheets("26 GA SP & GV")
    Set Ws = Worksheets("26 GA KY")
    Set Ws = Worksheets("24 GA KY")

    And the code always dies here:
    (different ranges of course per macro)
    Ws.Range("D2:J9").Sort Key1:=Ws.Range("G2"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

    I did pull one of the Macros out and put them in a module alone and still crashes.

    I think I am "oh so close" on this but with my limited VBA knowledge, can't say.

    So there's one Macro per sheet?



  • @dafyre said in Rewriting VBA Macros from Excel 2013 to Excel 2003 - Works One One Sheet Only.:

    @garak0410 said in Rewriting VBA Macros from Excel 2013 to Excel 2003 - Works One One Sheet Only.:

    @dafyre said in Rewriting VBA Macros from Excel 2013 to Excel 2003 - Works One One Sheet Only.:

    @garak0410 said in Rewriting VBA Macros from Excel 2013 to Excel 2003 - Works One One Sheet Only.:

    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:

    Sub Macro1()
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range( _
    "G2:G13"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("26 GA SP & GV").Sort
    .SetRange Range("D2:J13")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    End Sub

    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?

    Thanks...

    The way your new Macro is written, it looks like it's only meant to work on one worksheet.

    Can you loop through the worksheets and run the Macro on each one?

    Well, I've changed the Worksheet name on the other sheets to this:
    Set Ws = Worksheets("26 GA SP & GV")
    Set Ws = Worksheets("26 GA KY")
    Set Ws = Worksheets("24 GA KY")

    And the code always dies here:
    (different ranges of course per macro)
    Ws.Range("D2:J9").Sort Key1:=Ws.Range("G2"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

    I did pull one of the Macros out and put them in a module alone and still crashes.

    I think I am "oh so close" on this but with my limited VBA knowledge, can't say.

    So there's one Macro per sheet?

    On sheet one, called 26 GA SP & GV, there are 17 Macro's, all stored in individual modules in VBA called Module 1- Module 17.

    The second sheet is called 26 GA KY and it has 8 Macro's all stored under ONE Module called Module18

    All of the Macro's on sheet 26 GA SP & GV work fine and I simply copy and pasted the code, just changing the ranges.

    When it came to sheet 26 GA KY, I made sure it selected that sheet in the code and then changed the ranges for each Macro and that is when the errors come. I sure thought this would all work.



  • @garak0410 said in Rewriting VBA Macros from Excel 2013 to Excel 2003 - Works One One Sheet Only.:

    Ws.Range("D2:J9").Sort Key1:=Ws.Range("G2"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

    I can't help but notice on this line, the Ws.Range("G2") is that supposed to be a range or a single cell?

    If it's a single cell, what happens if you try Ws.Cell(2,"G") ?



  • @dafyre said in Rewriting VBA Macros from Excel 2013 to Excel 2003 - Works One One Sheet Only.:

    @garak0410 said in Rewriting VBA Macros from Excel 2013 to Excel 2003 - Works One One Sheet Only.:

    Ws.Range("D2:J9").Sort Key1:=Ws.Range("G2"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

    I can't help but notice on this line, the Ws.Range("G2") is that supposed to be a range or a single cell?

    If it's a single cell, what happens if you try Ws.Cell(2,"G") ?

    Good question as my "chicken scratch" VBA code sometimes didn't make sense that it worked at all. I'll give it a shot.

    What is mind-boggling is that the "G2" reference appears in all of the Macros, even their first line is not G2. And the Macros on the first page all work correctly, even with the G2 Reference in them. I'm sure this is mega easy but just can't find the problem.



  • I think I am closer...the first worksheet doesn't have column D protected. The rest of the pages do have column D protected. If I unprotect column D on the other sheets, my macros finally work.

    However, management wants column D protected.

    This worked fine under the recorded macros but not the rewritten ones I had to make to support Excel 2003.



  • @garak0410 said in Rewriting VBA Macros from Excel 2013 to Excel 2003 - Works One One Sheet Only.:

    I think I am closer...the first worksheet doesn't have column D protected. The rest of the pages do have column D protected. If I unprotect column D on the other sheets, my macros finally work.

    However, management wants column D protected.

    This worked fine under the recorded macros but not the rewritten ones I had to make to support Excel 2003.

    Have the Macro unprotect the column and then protect it again as the last step?


Log in to reply