ML
    • Recent
    • Categories
    • Tags
    • Popular
    • Users
    • Groups
    • Register
    • Login

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

    IT Discussion
    2
    9
    1.3k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • garak0410G
      garak0410
      last edited by

      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...

      dafyreD 1 Reply Last reply Reply Quote 0
      • dafyreD
        dafyre @garak0410
        last edited by

        @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?

        garak0410G 1 Reply Last reply Reply Quote 0
        • garak0410G
          garak0410 @dafyre
          last edited by

          @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.

          dafyreD 2 Replies Last reply Reply Quote 0
          • dafyreD
            dafyre @garak0410
            last edited by

            @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?

            garak0410G 1 Reply Last reply Reply Quote 0
            • garak0410G
              garak0410 @dafyre
              last edited by

              @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.

              1 Reply Last reply Reply Quote 0
              • dafyreD
                dafyre @garak0410
                last edited by

                @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") ?

                garak0410G 1 Reply Last reply Reply Quote 0
                • garak0410G
                  garak0410 @dafyre
                  last edited by garak0410

                  @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.

                  1 Reply Last reply Reply Quote 0
                  • garak0410G
                    garak0410
                    last edited by

                    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.

                    dafyreD 1 Reply Last reply Reply Quote 0
                    • dafyreD
                      dafyre @garak0410
                      last edited by

                      @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?

                      1 Reply Last reply Reply Quote 1
                      • 1 / 1
                      • First post
                        Last post