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.

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

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

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

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

@dashrender said in Production / Enterprise Level Printing With Multiple Sorter/Mailbox Trays:

So you have few enough users to make the number of trays useful, OK.

Yes and in fact, we need 12 output trays if we could.

The biggest inconvenience (and productivity killer) for management the time spent employees will spend sorting drawings if we have to settle for one tray.

Here is our, now dead (RIP) 9040...we lived by that output tray.

After a lot of searching today and two calls from vendors, it looks like meeting all of our requirements may be impossible.

We found several Lexmark printers that have stackable output bins but no 11x17. They both said there hasn't been much demand for what we had in our 9040.

I'm in the midst of a difficult search. Our trusty HP 9040 with an 8 tray sorter/mailbox has died on us...

Upon searching, I can't seem to find anything close to this. HP makes a sorter but mainly for an option to have a stapler. I've checked Lexmark and they have some options but not sure about the quality and if we can assign trays per user like we can with the HP.

The craziest part, is my preferred vendors are dragging their feet on researching and quoting options for me. I am doing my own searches but I might not blame them. It has been difficult...

Our needs:

Enterprise/Production Level Printing
Trays: Letter, Legal, 11x17
The most output bins we can get these days.

Any suggestions?

I have a Surface Book (i7, 512GB, 16GB RAM, Nvidia GPU) that has been "good enough" for me on mobile gaming and has played everything I've thrown at it. I have, though, given thought on selling it, while resale value is still high, for a good gaming laptop. Very tempted...