Print Sheets In Time Order

[Barryj]

I am trying to come up with a way to print sheets in a time order, I have attached a work sheet showing a sample of how the data is on each sheet.

What I need to do is starting in row 5 column 3 of each sheet get the earliest time to latest time print these sheets, if row 5 is blank or has text then skip.

Then row 6 earliest to latest time print these sheets skip blanks or if it has text.

Then row 7 earliest to latest time print these sheets skip blanks or if it has text.

Then print all sheets not yet printed.

Is there a way of doing this simply or is it to complicated, there are approximately 100 sheets in the work book.



Any ideas would be greatly received.

[GTO]

At least for me, I am not exactly following your description of what we are checking, vs. what we are printing?

[Barryj]

Basically what I am trying to do is if time on sheet 1 is 06:00 and time on sheet 2 is 04:00 the sheet 2 prints first, there is about 100 sheet in the actual work book.

So I was wanting it to check row 5 of each sheet skip blanks or text, then row 6 and then row 7 and then print all other sheets after that.

I hope that makes things a little clearer!

Thanks for looking

[GTO]

Hi again Barry,

Okay, I think I am catching on. Let me see if I summarize this correctly.

For ea sheet:
Check rows 5 to 7.
If we find a legitimate time value, save this in some sort of collection.
If we don't find a time, disregard for the moment.
After building our collection of times, print the sheets in time order, after which print remaining sheets.Now you did mention an earliest to latest time to print in your first post. Does that mean literally the code needs to keep running and print at the actual times listed, or just that we want to print them in 'time order'.

[Barryj]

Yes that is correct GTO, your discribtion of how it should work in your above post is correct .

I guess it's like printing from lowest to highest as far as time order goes.

Thanks for the assistance.

[GTO]

Thank you and my bad, I should have spotted this before. In your attachment, the "times" are actually text (string) values, as the cells have been formatted as Text. In your real workbook, are these text values as well? It shouldn't be a problem, but would require some differences in approach.

[Barryj]

Yes they are text, I have another macro that copies them over to this sheet as text.

[GTO]

Try:

Option Explicit

Sub Main()
Dim DIC As Object ' Dictionary
Dim wks As Worksheet
Dim Numbers As Variant
Dim ShNames As Variant
Dim Times As Variant
Dim i As Long
Dim bolFoundTime As Boolean

Set DIC = CreateObject("Scripting.Dictionary")

For Each wks In ThisWorkbook.Worksheets
bolFoundTime = False
For i = 5 To 7
If wks.Cells(i, 3).Text Like "##:##" Then
Numbers = Split(wks.Cells(i, 3).Text, ":")
If Numbers(0) >= 0 And Numbers(0) <= 23 And Numbers(1) > 0 And Numbers(1) <= 59 Then
DIC.Item(wks.Name) = CDbl(TimeSerial(Numbers(0), Numbers(1), 0))
bolFoundTime = True
Exit For
End If
End If
Next
If Not bolFoundTime Then
DIC.Item(wks.Name) = 0.999988425925926
End If
Next

ShNames = DIC.Keys
Times = DIC.Items

Set wks = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1), Type:=xlWBATWorksheet)
With wks
.Range("A1").Resize(UBound(Times) + 1).Value = Application.Transpose(Times)
.Range("B1").Resize(UBound(Times) + 1).Value = Application.Transpose(ShNames)
.Range("A1").Resize(UBound(Times) + 1, 2).Sort .Range("A1"), xlAscending
ShNames = .Range("B1").Resize(UBound(Times) + 1).Value
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

For i = LBound(ShNames, 1) To UBound(ShNames, 1)

MsgBox "ThisWorkbook.Worksheets(" & ShNames(i, 1) & ").PrintOut"
'ThisWorkbook.Worksheets(ShNames(i,1)).PrintOut
DoEvents
Next
End Sub

[Barryj]

Thanks for the help GTO, I think I have made an error in my discription, when I described that there was 100 sheet, I should have said 100 pages on that sheet, as in 100 pages in sheet 1.

My mind was on the fact it prints out 100 sheets.

As in the example workbook the pages are all contained within the one sheet.

Sorry for the confusion.