I have a macro that prints several pdf's for schedules from one sheet. It takes about 10 minutes for the macro to run and make the 10 pdf's. Is there a way to make this run faster?
VBA Code:
Sub Create_Schedules()
Sheet1.Activate
ActiveWindow.View = xlPageBreakPreview
Dim Yellow As String
Yellow = ThisWorkbook.Worksheets("Backend").Range("D1").Interior.Color
Dim Grey As String
Grey = ThisWorkbook.Worksheets("Backend").Range("F1").Interior.Color
Dim LightBlue As String
LightBlue = ThisWorkbook.Worksheets("Backend").Range("H1").Interior.Color
Dim Green As String
Green = ThisWorkbook.Worksheets("Backend").Range("J1").Interior.Color
Dim a As Date
a = Application.WorksheetFunction.WorkDay(Date, 2, ThisWorkbook.Worksheets("Backend").Range("B1:B7"))
Dim tomorrow As Date
tomorrow = Application.WorksheetFunction.WorkDay(Date, 1, ThisWorkbook.Worksheets("Backend").Range("B1:B7"))
Dim b As Integer
Dim i As Integer
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim totalPages As Integer
Dim h As Integer
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''' PRELIMINARY EDITS '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Unfilter all, Change Title and hide columns in beginning of spreadsheet
Worksheets("Master Schedule").Columns.Hidden = False
Worksheets("Master Schedule").Rows.Hidden = False
Worksheets("Master Schedule").Range("A1").Value = "Brimar Wood Department Schedule for " & tomorrow
Worksheets("Master Schedule").Columns("C").EntireColumn.Hidden = True
Worksheets("Master Schedule").Columns("I:O").EntireColumn.Hidden = True
'Worksheets("Master Schedule").PageSetup.Orientation = xlLandscape
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''' SHIP SCHEDULE '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Unhide all rows and hide BOM/OPT and Programming columns
Worksheets("Master Schedule").Rows.EntireRow.Hidden = False
Worksheets("Master Schedule").Range("P:BQ").EntireColumn.Hidden = True
Worksheets("Master Schedule").Range("BR:BT").EntireColumn.Hidden = False
Worksheets("Master Schedule").Range("BU:BW").EntireColumn.Hidden = True
Worksheets("Master Schedule").Range("BX:BX").EntireColumn.Hidden = False
Worksheets("Master Schedule").Range("BY:BY").EntireColumn.Hidden = True
'Hide rows based on criteria
Dim CellSHIP As Range
Dim RngSHIP As Range
Set RngSHIP = Range("BR3:BR1000")
For Each CellSHIP In RngSHIP
If CellSHIP.Interior.Color = Grey Or CellSHIP.Interior.Color = LightBlue Or CellSHIP.Value = "NA" Or CellSHIP.Value = "" Then
CellSHIP.EntireRow.Hidden = True
End If
Next CellSHIP
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Ship.pdf", OpenAfterPublish:=True
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''' PACK SCHEDULE '''''````''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Unhide all rows and hide BOM/OPT and Programming columns
Worksheets("Master Schedule").Rows.EntireRow.Hidden = False
Worksheets("Master Schedule").Range("P:BM").EntireColumn.Hidden = True
Worksheets("Master Schedule").Range("BN:BO").EntireColumn.Hidden = False
Worksheets("Master Schedule").Range("BP:BY").EntireColumn.Hidden = True
'Hide rows based on criteria
Dim CellPACK As Range
Dim RngPACK As Range
Set RngPACK = Range("BO3:BO1000")
For Each CellPACK In RngPACK
If CellPACK.Interior.Color = Grey Or CellPACK.Interior.Color = Green Or CellPACK.Interior.Color = LightBlue Or CellPACK.Value = "" Or CellPACK.Value = "NA" Then
CellPACK.EntireRow.Hidden = True
End If
Next CellPACK
'Sort by date
Range("A3:BY1000").Sort Key1:=Range("BO3"), Order1:=xlAscending
'Turn screen updating off for page break calculations because Excel is dumb
'If a schedule is 2 pages or less, print all dates otherwise hide all rows past tomorrow's date (a) and then print the remaining data.
totalPages = Worksheets("Master Schedule").HPageBreaks.Count + 1
If totalPages <= 2 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pack.pdf", OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(2).Location.Row, 67).Value > a Or totalPages = 3 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pack.pdf", From:=1, To:=3, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(3).Location.Row, 67).Value > a Or totalPages = 4 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pack.pdf", From:=1, To:=4, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(4).Location.Row, 67).Value > a Or totalPages = 5 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pack.pdf", From:=1, To:=5, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(5).Location.Row, 67).Value > a Or totalPages = 6 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pack.pdf", From:=1, To:=6, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(6).Location.Row, 67).Value > a Or totalPages = 7 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pack.pdf", From:=1, To:=7, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(7).Location.Row, 67).Value > a Or totalPages = 8 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pack.pdf", From:=1, To:=8, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(8).Location.Row, 67).Value > a Or totalPages = 9 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pack.pdf", From:=1, To:=9, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(9).Location.Row, 67).Value > a Or totalPages = 10 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pack.pdf", From:=1, To:=10, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(10).Location.Row, 67).Value > a Or totalPages = 11 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pack.pdf", From:=1, To:=11, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(11).Location.Row, 67).Value > a Or totalPages = 12 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pack.pdf", From:=1, To:=12, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(12).Location.Row, 67).Value > a Or totalPages = 13 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pack.pdf", From:=1, To:=13, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(13).Location.Row, 67).Value > a Or totalPages = 14 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pack.pdf", From:=1, To:=14, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(14).Location.Row, 67).Value > a Or totalPages = 15 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pack.pdf", From:=1, To:=15, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(15).Location.Row, 67).Value > a Or totalPages = 16 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pack.pdf", From:=1, To:=16, OpenAfterPublish:=True
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''' BOM SCHEDULE ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Hide columns after BOM/OPT
Worksheets("Master Schedule").Rows.EntireRow.Hidden = False
Worksheets("Master Schedule").Range("P:Q").EntireColumn.Hidden = False
Worksheets("Master Schedule").Range("R:BY").EntireColumn.Hidden = True
'Hide rows based on criteria
Dim CellBOM As Range
Dim RngBOM As Range
Set RngBOM = Range("P3:P1000")
For Each CellBOM In RngBOM
If CellBOM.Interior.Color = Grey Or CellBOM.Interior.Color = Green Or CellBOM.Interior.Color = LightBlue Or CellBOM.Value = "NA" Or CellBOM.Value = "" Then
CellBOM.EntireRow.Hidden = True
End If
Next CellBOM
'Sort by date
Range("A3:BY1000").Sort Key1:=Range("P3"), Order1:=xlAscending
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/BOM.pdf", OpenAfterPublish:=True
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''' PROGRAMMING SCHEDULE ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Unhide all rows and hide BOM/OPT columns and columns after programming
Worksheets("Master Schedule").Rows.EntireRow.Hidden = False
Worksheets("Master Schedule").Range("P:Q").EntireColumn.Hidden = True
Worksheets("Master Schedule").Range("R:R").EntireColumn.Hidden = False
Worksheets("Master Schedule").Range("S:BY").EntireColumn.Hidden = True
'Hide rows based on criteria
Dim CellPROG As Range
Dim RngPROG As Range
Set RngPROG = Range("R3:R1000")
For Each CellPROG In RngPROG
If CellPROG.Interior.Color = Grey Or CellPROG.Interior.Color = Green Or CellPROG.Interior.Color = LightBlue Or CellPROG.Value = "NA" Or CellPROG.Value = "" Then
CellPROG.EntireRow.Hidden = True
End If
Next CellPROG
'Sort by date
Range("A3:BY1000").Sort Key1:=Range("R3"), Order1:=xlAscending
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Programming.pdf", OpenAfterPublish:=True
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''' KITTING SCHEDULE ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Unhide all rows and hide BOM/OPT columns and columns after programming
Worksheets("Master Schedule").Rows.EntireRow.Hidden = False
Worksheets("Master Schedule").Range("P:R").EntireColumn.Hidden = True
Worksheets("Master Schedule").Range("S:S").EntireColumn.Hidden = False
Worksheets("Master Schedule").Range("T:BY").EntireColumn.Hidden = True
'Hide rows based on criteria
Dim CellKITT As Range
Dim RngKITT As Range
Set RngKITT = Range("S3:S1000")
For Each CellKITT In RngKITT
If CellKITT.Interior.Color = Grey Or CellKITT.Interior.Color = Green Or CellKITT.Interior.Color = LightBlue Or CellKITT.Value = "" Or CellKITT.Value = "NA" Then
CellKITT.EntireRow.Hidden = True
End If
Next CellKITT
'Sort by date
Range("A3:BY1000").Sort Key1:=Range("S3"), Order1:=xlAscending
' Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Kitting.pdf", OpenAfterPublish:=True
'If a schedule is 2 pages or less, print all dates otherwise hide all rows past tomorrow's date (a) and then print the remaining data.
totalPages = Worksheets("Master Schedule").HPageBreaks.Count + 1
If totalPages <= 2 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Kitting.pdf", OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(2).Location.Row, 19).Value > a Or totalPages = 3 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Kitting.pdf", From:=1, To:=3, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(3).Location.Row, 19).Value > a Or totalPages = 4 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Kitting.pdf", From:=1, To:=4, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(4).Location.Row, 19).Value > a Or totalPages = 5 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Kitting.pdf", From:=1, To:=5, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(5).Location.Row, 19).Value > a Or totalPages = 6 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Kitting.pdf", From:=1, To:=6, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(6).Location.Row, 19).Value > a Or totalPages = 7 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Kitting.pdf", From:=1, To:=7, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(7).Location.Row, 19).Value > a Or totalPages = 8 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Kitting.pdf", From:=1, To:=8, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(8).Location.Row, 19).Value > a Or totalPages = 9 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Kitting.pdf", From:=1, To:=9, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(9).Location.Row, 19).Value > a Or totalPages = 10 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Kitting.pdf", From:=1, To:=10, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(10).Location.Row, 19).Value > a Or totalPages = 11 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Kitting.pdf", From:=1, To:=11, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(11).Location.Row, 19).Value > a Or totalPages = 12 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Kitting.pdf", From:=1, To:=12, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(12).Location.Row, 19).Value > a Or totalPages = 13 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Kitting.pdf", From:=1, To:=13, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(13).Location.Row, 19).Value > a Or totalPages = 14 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Kitting.pdf", From:=1, To:=14, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(14).Location.Row, 19).Value > a Or totalPages = 15 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Kitting.pdf", From:=1, To:=15, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(15).Location.Row, 19).Value > a Or totalPages = 16 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Kitting.pdf", From:=1, To:=16, OpenAfterPublish:=True
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''' FACEMAKING SCHEDULE '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Unhide all rows and hide BOM/OPT and Programming columns
Worksheets("Master Schedule").Rows.EntireRow.Hidden = False
Worksheets("Master Schedule").Range("O:U").EntireColumn.Hidden = True
Worksheets("Master Schedule").Range("V:W").EntireColumn.Hidden = False
Worksheets("Master Schedule").Range("X:BY").EntireColumn.Hidden = True
'Hide rows based on criteria
Dim CellFACE As Range
Dim RngFACE As Range
Set RngFACE = Range("W3:W1000")
For Each CellFACE In RngFACE
If CellFACE.Interior.Color = Grey Or CellFACE.Interior.Color = Green Or CellFACE.Interior.Color = LightBlue Or CellFACE.Value = "" Or CellFACE.Value = "NA" Then
CellFACE.EntireRow.Hidden = True
End If
Next CellFACE
'Sort by date
Range("A3:BY1000").Sort Key1:=Range("W3"), Order1:=xlAscending
'Turn screen updating off for page break calculations because Excel is dumb
'If a schedule is 2 pages or less, print all dates otherwise hide all rows past tomorrow's date (a) and then print the remaining data.
totalPages = Worksheets("Master Schedule").HPageBreaks.Count + 1
If totalPages <= 2 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Facemaking.pdf", OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(2).Location.Row, 23).Value > a Or totalPages = 3 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Facemaking.pdf", From:=1, To:=3, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(3).Location.Row, 23).Value > a Or totalPages = 4 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Facemaking.pdf", From:=1, To:=4, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(4).Location.Row, 23).Value > a Or totalPages = 5 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Facemaking.pdf", From:=1, To:=5, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(5).Location.Row, 23).Value > a Or totalPages = 6 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Facemaking.pdf", From:=1, To:=6, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(6).Location.Row, 23).Value > a Or totalPages = 7 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Facemaking.pdf", From:=1, To:=7, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(7).Location.Row, 23).Value > a Or totalPages = 8 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Facemaking.pdf", From:=1, To:=8, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(8).Location.Row, 23).Value > a Or totalPages = 9 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Facemaking.pdf", From:=1, To:=9, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(9).Location.Row, 23).Value > a Or totalPages = 10 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Facemaking.pdf", From:=1, To:=10, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(10).Location.Row, 23).Value > a Or totalPages = 11 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Facemaking.pdf", From:=1, To:=11, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(11).Location.Row, 23).Value > a Or totalPages = 12 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Facemaking.pdf", From:=1, To:=12, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(12).Location.Row, 23).Value > a Or totalPages = 13 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Facemaking.pdf", From:=1, To:=13, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(13).Location.Row, 23).Value > a Or totalPages = 14 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Facemaking.pdf", From:=1, To:=14, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(14).Location.Row, 23).Value > a Or totalPages = 15 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Facemaking.pdf", From:=1, To:=15, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(15).Location.Row, 23).Value > a Or totalPages = 16 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Facemaking.pdf", From:=1, To:=16, OpenAfterPublish:=True
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''' CORE CUTTING SCHEDULE '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Unhide all rows and hide BOM/OPT and Programming columns
Worksheets("Master Schedule").Rows.EntireRow.Hidden = False
Worksheets("Master Schedule").Range("P:Y").EntireColumn.Hidden = True
Worksheets("Master Schedule").Range("Z:AA").EntireColumn.Hidden = False
Worksheets("Master Schedule").Range("AB:BY").EntireColumn.Hidden = True
'Hide rows based on criteria
Dim CellCORE As Range
Dim RngCORE As Range
Set RngCORE = Range("AA3:AA1000")
For Each CellCORE In RngCORE
If CellCORE.Interior.Color = Grey Or CellCORE.Interior.Color = Green Or CellCORE.Interior.Color = LightBlue Or CellCORE.Value = "" Or CellCORE.Value = "NA" Then
CellCORE.EntireRow.Hidden = True
End If
Next CellCORE
'Sort by date
Range("A3:BY1000").Sort Key1:=Range("AA3"), Order1:=xlAscending
'Turn screen updating off for page break calculations because Excel is dumb
'If a schedule is 2 pages or less, print all dates otherwise hide all rows past tomorrow's date (a) and then print the remaining data.
totalPages = Worksheets("Master Schedule").HPageBreaks.Count + 1
If totalPages <= 2 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CoreCutting.pdf", OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(2).Location.Row, 27).Value > a Or totalPages = 3 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CoreCutting.pdf", From:=1, To:=3, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(3).Location.Row, 27).Value > a Or totalPages = 4 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CoreCutting.pdf", From:=1, To:=4, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(4).Location.Row, 27).Value > a Or totalPages = 5 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CoreCutting.pdf", From:=1, To:=5, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(5).Location.Row, 27).Value > a Or totalPages = 6 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CoreCutting.pdf", From:=1, To:=6, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(6).Location.Row, 27).Value > a Or totalPages = 7 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CoreCutting.pdf", From:=1, To:=7, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(7).Location.Row, 27).Value > a Or totalPages = 8 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CoreCutting.pdf", From:=1, To:=8, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(8).Location.Row, 27).Value > a Or totalPages = 9 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CoreCutting.pdf", From:=1, To:=9, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(9).Location.Row, 27).Value > a Or totalPages = 10 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CoreCutting.pdf", From:=1, To:=10, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(10).Location.Row, 27).Value > a Or totalPages = 11 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CoreCutting.pdf", From:=1, To:=11, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(11).Location.Row, 27).Value > a Or totalPages = 12 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CoreCutting.pdf", From:=1, To:=12, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(12).Location.Row, 27).Value > a Or totalPages = 13 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CoreCutting.pdf", From:=1, To:=13, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(13).Location.Row, 27).Value > a Or totalPages = 14 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CoreCutting.pdf", From:=1, To:=14, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(14).Location.Row, 27).Value > a Or totalPages = 15 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CoreCutting.pdf", From:=1, To:=15, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(15).Location.Row, 27).Value > a Or totalPages = 16 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CoreCutting.pdf", From:=1, To:=16, OpenAfterPublish:=True
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''' PRESS SCHEDULE ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Unhide all rows and hide BOM/OPT and Programming columns
Worksheets("Master Schedule").Rows.EntireRow.Hidden = False
Worksheets("Master Schedule").Range("P:AC").EntireColumn.Hidden = True
Worksheets("Master Schedule").Range("AD:AE").EntireColumn.Hidden = False
Worksheets("Master Schedule").Range("AF:BY").EntireColumn.Hidden = True
'Hide rows based on criteria
Dim CellPRESS As Range
Dim RngPRESS As Range
Set RngPRESS = Range("AE3:AE1000")
For Each CellPRESS In RngPRESS
If CellPRESS.Interior.Color = Grey Or CellPRESS.Interior.Color = Green Or CellPRESS.Interior.Color = LightBlue Or CellPRESS.Value = "" Or CellPRESS.Value = "NA" Then
CellPRESS.EntireRow.Hidden = True
End If
Next CellPRESS
'Sort by date
Range("A3:BY1000").Sort Key1:=Range("AE3"), Order1:=xlAscending
'Turn screen updating off for page break calculations because Excel is dumb
'If a schedule is 2 pages or less, print all dates otherwise hide all rows past tomorrow's date (a) and then print the remaining data.
totalPages = Worksheets("Master Schedule").HPageBreaks.Count + 1
If totalPages <= 2 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Press.pdf", OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(2).Location.Row, 31).Value > a Or totalPages = 3 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Press.pdf", From:=1, To:=3, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(3).Location.Row, 31).Value > a Or totalPages = 4 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Press.pdf", From:=1, To:=4, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(4).Location.Row, 31).Value > a Or totalPages = 5 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Press.pdf", From:=1, To:=5, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(5).Location.Row, 31).Value > a Or totalPages = 6 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Press.pdf", From:=1, To:=6, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(6).Location.Row, 31).Value > a Or totalPages = 7 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Press.pdf", From:=1, To:=7, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(7).Location.Row, 31).Value > a Or totalPages = 8 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Press.pdf", From:=1, To:=8, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(8).Location.Row, 31).Value > a Or totalPages = 9 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Press.pdf", From:=1, To:=9, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(9).Location.Row, 31).Value > a Or totalPages = 10 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Press.pdf", From:=1, To:=10, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(10).Location.Row, 31).Value > a Or totalPages = 11 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Press.pdf", From:=1, To:=11, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(11).Location.Row, 31).Value > a Or totalPages = 12 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Press.pdf", From:=1, To:=12, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(12).Location.Row, 31).Value > a Or totalPages = 13 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Press.pdf", From:=1, To:=13, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(13).Location.Row, 31).Value > a Or totalPages = 14 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Press.pdf", From:=1, To:=14, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(14).Location.Row, 31).Value > a Or totalPages = 15 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Press.pdf", From:=1, To:=15, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(15).Location.Row, 31).Value > a Or totalPages = 16 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Press.pdf", From:=1, To:=16, OpenAfterPublish:=True
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''' PANEL SAW SCHEDULE ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Unhide all rows and hide BOM/OPT and Programming columns
Worksheets("Master Schedule").Rows.EntireRow.Hidden = False
Worksheets("Master Schedule").Range("P:AG").EntireColumn.Hidden = True
Worksheets("Master Schedule").Range("AH:AI").EntireColumn.Hidden = False
Worksheets("Master Schedule").Range("AJ:BY").EntireColumn.Hidden = True
'Hide rows based on criteria
Dim CellPSAW As Range
Dim RngPSAW As Range
Set RngPSAW = Range("AI3:AI1000")
For Each CellPSAW In RngPSAW
If CellPSAW.Interior.Color = Grey Or CellPSAW.Interior.Color = Green Or CellPSAW.Interior.Color = LightBlue Or CellPSAW.Value = "" Or CellPSAW.Value = "NA" Then
CellPSAW.EntireRow.Hidden = True
End If
Next CellPSAW
'Sort by date
Range("A3:BY1000").Sort Key1:=Range("AI3"), Order1:=xlAscending
'Turn screen updating off for page break calculations because Excel is dumb
'If a schedule is 2 pages or less, print all dates otherwise hide all rows past tomorrow's date (a) and then print the remaining data.
totalPages = Worksheets("Master Schedule").HPageBreaks.Count + 1
If totalPages <= 2 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/PanelSaw.pdf", OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(2).Location.Row, 35).Value > a Or totalPages = 3 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/PanelSaw.pdf", From:=1, To:=3, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(3).Location.Row, 35).Value > a Or totalPages = 4 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/PanelSaw.pdf", From:=1, To:=4, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(4).Location.Row, 35).Value > a Or totalPages = 5 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/PanelSaw.pdf", From:=1, To:=5, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(5).Location.Row, 35).Value > a Or totalPages = 6 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/PanelSaw.pdf", From:=1, To:=6, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(6).Location.Row, 35).Value > a Or totalPages = 7 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/PanelSaw.pdf", From:=1, To:=7, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(7).Location.Row, 35).Value > a Or totalPages = 8 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/PanelSaw.pdf", From:=1, To:=8, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(8).Location.Row, 35).Value > a Or totalPages = 9 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/PanelSaw.pdf", From:=1, To:=9, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(9).Location.Row, 35).Value > a Or totalPages = 10 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/PanelSaw.pdf", From:=1, To:=10, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(10).Location.Row, 35).Value > a Or totalPages = 11 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/PanelSaw.pdf", From:=1, To:=11, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(11).Location.Row, 35).Value > a Or totalPages = 12 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/PanelSaw.pdf", From:=1, To:=12, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(12).Location.Row, 35).Value > a Or totalPages = 13 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/PanelSaw.pdf", From:=1, To:=13, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(13).Location.Row, 35).Value > a Or totalPages = 14 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/PanelSaw.pdf", From:=1, To:=14, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(14).Location.Row, 35).Value > a Or totalPages = 15 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/PanelSaw.pdf", From:=1, To:=15, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(15).Location.Row, 35).Value > a Or totalPages = 16 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/PanelSaw.pdf", From:=1, To:=16, OpenAfterPublish:=True
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''' EDGEBAND SCHEDULE '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Unhide all rows and hide BOM/OPT and Programming columns
Worksheets("Master Schedule").Rows.EntireRow.Hidden = False
Worksheets("Master Schedule").Range("P:AK").EntireColumn.Hidden = True
Worksheets("Master Schedule").Range("AL:AM").EntireColumn.Hidden = False
Worksheets("Master Schedule").Range("AN:BY").EntireColumn.Hidden = True
'Hide rows based on criteria
Dim CellEDGE As Range
Dim RngEDGE As Range
Set RngEDGE = Range("AM3:AM1000")
For Each CellEDGE In RngEDGE
If CellEDGE.Interior.Color = Grey Or CellEDGE.Interior.Color = Green Or CellEDGE.Interior.Color = LightBlue Or CellEDGE.Value = "" Or CellEDGE.Value = "NA" Then
CellEDGE.EntireRow.Hidden = True
End If
Next CellEDGE
'Sort by date
Range("A3:BY1000").Sort Key1:=Range("AM3"), Order1:=xlAscending
'Turn screen updating off for page break calculations because Excel is dumb
'If a schedule is 2 pages or less, print all dates otherwise hide all rows past tomorrow's date (a) and then print the remaining data.
totalPages = Worksheets("Master Schedule").HPageBreaks.Count + 1
If totalPages <= 2 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Edgeband.pdf", OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(2).Location.Row, 39).Value > a Or totalPages = 3 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Edgeband.pdf", From:=1, To:=3, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(3).Location.Row, 39).Value > a Or totalPages = 4 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Edgeband.pdf", From:=1, To:=4, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(4).Location.Row, 39).Value > a Or totalPages = 5 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Edgeband.pdf", From:=1, To:=5, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(5).Location.Row, 39).Value > a Or totalPages = 6 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Edgeband.pdf", From:=1, To:=6, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(6).Location.Row, 39).Value > a Or totalPages = 7 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Edgeband.pdf", From:=1, To:=7, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(7).Location.Row, 39).Value > a Or totalPages = 8 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Edgeband.pdf", From:=1, To:=8, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(8).Location.Row, 39).Value > a Or totalPages = 9 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Edgeband.pdf", From:=1, To:=9, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(9).Location.Row, 39).Value > a Or totalPages = 10 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Edgeband.pdf", From:=1, To:=10, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(10).Location.Row, 39).Value > a Or totalPages = 11 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Edgeband.pdf", From:=1, To:=11, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(11).Location.Row, 39).Value > a Or totalPages = 12 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Edgeband.pdf", From:=1, To:=12, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(12).Location.Row, 39).Value > a Or totalPages = 13 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Edgeband.pdf", From:=1, To:=13, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(13).Location.Row, 39).Value > a Or totalPages = 14 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Edgeband.pdf", From:=1, To:=14, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(14).Location.Row, 39).Value > a Or totalPages = 15 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Edgeband.pdf", From:=1, To:=15, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(15).Location.Row, 39).Value > a Or totalPages = 16 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Edgeband.pdf", From:=1, To:=16, OpenAfterPublish:=True
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''' CNC SCHEDULE ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Unhide all rows and hide BOM/OPT and Programming columns
Worksheets("Master Schedule").Rows.EntireRow.Hidden = False
Worksheets("Master Schedule").Range("P:AO").EntireColumn.Hidden = True
Worksheets("Master Schedule").Range("AP:AQ").EntireColumn.Hidden = False
Worksheets("Master Schedule").Range("AR:BY").EntireColumn.Hidden = True
'Hide rows based on criteria
Dim CellCNC As Range
Dim RngCNC As Range
Set RngCNC = Range("AQ3:AQ1000")
For Each CellCNC In RngCNC
If CellCNC.Interior.Color = Grey Or CellCNC.Interior.Color = Green Or CellCNC.Interior.Color = LightBlue Or CellCNC.Value = "" Or CellCNC.Value = "NA" Then
CellCNC.EntireRow.Hidden = True
End If
Next CellCNC
'Sort by date
Range("A3:BY1000").Sort Key1:=Range("AQ3"), Order1:=xlAscending
'Turn screen updating off for page break calculations because Excel is dumb
'If a schedule is 2 pages or less, print all dates otherwise hide all rows past tomorrow's date (a) and then print the remaining data.
totalPages = Worksheets("Master Schedule").HPageBreaks.Count + 1
If totalPages <= 2 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CNC.pdf", OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(2).Location.Row, 43).Value > a Or totalPages = 3 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CNC.pdf", From:=1, To:=3, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(3).Location.Row, 43).Value > a Or totalPages = 4 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CNC.pdf", From:=1, To:=4, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(4).Location.Row, 43).Value > a Or totalPages = 5 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CNC.pdf", From:=1, To:=5, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(5).Location.Row, 43).Value > a Or totalPages = 6 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CNC.pdf", From:=1, To:=6, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(6).Location.Row, 43).Value > a Or totalPages = 7 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CNC.pdf", From:=1, To:=7, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(7).Location.Row, 43).Value > a Or totalPages = 8 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CNC.pdf", From:=1, To:=8, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(8).Location.Row, 43).Value > a Or totalPages = 9 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CNC.pdf", From:=1, To:=9, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(9).Location.Row, 43).Value > a Or totalPages = 10 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CNC.pdf", From:=1, To:=10, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(10).Location.Row, 43).Value > a Or totalPages = 11 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CNC.pdf", From:=1, To:=11, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(11).Location.Row, 43).Value > a Or totalPages = 12 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CNC.pdf", From:=1, To:=12, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(12).Location.Row, 43).Value > a Or totalPages = 13 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CNC.pdf", From:=1, To:=13, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(13).Location.Row, 43).Value > a Or totalPages = 14 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CNC.pdf", From:=1, To:=14, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(14).Location.Row, 43).Value > a Or totalPages = 15 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CNC.pdf", From:=1, To:=15, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(15).Location.Row, 43).Value > a Or totalPages = 16 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/CNC.pdf", From:=1, To:=16, OpenAfterPublish:=True
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''' SAW MAN SCHEDULE ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Unhide all rows and hide BOM/OPT and Programming columns
Worksheets("Master Schedule").Rows.EntireRow.Hidden = False
Worksheets("Master Schedule").Range("P:AS").EntireColumn.Hidden = True
Worksheets("Master Schedule").Range("AT:AU").EntireColumn.Hidden = False
Worksheets("Master Schedule").Range("AV:BY").EntireColumn.Hidden = True
'Hide rows based on criteria
Dim CellSMAN As Range
Dim RngSMAN As Range
Set RngSMAN = Range("AU3:AU1000")
For Each CellSMAN In RngSMAN
If CellSMAN.Interior.Color = Grey Or CellSMAN.Interior.Color = Green Or CellSMAN.Interior.Color = LightBlue Or CellSMAN.Value = "" Or CellSMAN.Value = "NA" Then
CellSMAN.EntireRow.Hidden = True
End If
Next CellSMAN
'Sort by date
Range("A3:BY1000").Sort Key1:=Range("AU3"), Order1:=xlAscending
'Turn screen updating off for page break calculations because Excel is dumb
'If a schedule is 2 pages or less, print all dates otherwise hide all rows past tomorrow's date (a) and then print the remaining data.
totalPages = Worksheets("Master Schedule").HPageBreaks.Count + 1
If totalPages <= 2 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/SawMan.pdf", OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(2).Location.Row, 47).Value > a Or totalPages = 3 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/SawMan.pdf", From:=1, To:=3, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(3).Location.Row, 47).Value > a Or totalPages = 4 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/SawMan.pdf", From:=1, To:=4, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(4).Location.Row, 47).Value > a Or totalPages = 5 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/SawMan.pdf", From:=1, To:=5, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(5).Location.Row, 47).Value > a Or totalPages = 6 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/SawMan.pdf", From:=1, To:=6, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(6).Location.Row, 47).Value > a Or totalPages = 7 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/SawMan.pdf", From:=1, To:=7, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(7).Location.Row, 47).Value > a Or totalPages = 8 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/SawMan.pdf", From:=1, To:=8, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(8).Location.Row, 47).Value > a Or totalPages = 9 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/SawMan.pdf", From:=1, To:=9, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(9).Location.Row, 47).Value > a Or totalPages = 10 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/SawMan.pdf", From:=1, To:=10, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(10).Location.Row, 47).Value > a Or totalPages = 11 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/SawMan.pdf", From:=1, To:=11, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(11).Location.Row, 47).Value > a Or totalPages = 12 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/SawMan.pdf", From:=1, To:=12, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(12).Location.Row, 47).Value > a Or totalPages = 13 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/SawMan.pdf", From:=1, To:=13, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(13).Location.Row, 47).Value > a Or totalPages = 14 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/SawMan.pdf", From:=1, To:=14, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(14).Location.Row, 47).Value > a Or totalPages = 15 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/SawMan.pdf", From:=1, To:=15, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(15).Location.Row, 47).Value > a Or totalPages = 16 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/SawMan.pdf", From:=1, To:=16, OpenAfterPublish:=True
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ASSEMBLY/SAND SCHEDULE ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Unhide all rows and hide BOM/OPT and Programming columns
Worksheets("Master Schedule").Rows.EntireRow.Hidden = False
Worksheets("Master Schedule").Range("P:AW").EntireColumn.Hidden = True
Worksheets("Master Schedule").Range("AX:AY").EntireColumn.Hidden = False
Worksheets("Master Schedule").Range("AZ:BY").EntireColumn.Hidden = True
'Hide rows based on criteria
Dim CellASEM As Range
Dim RngASEM As Range
Set RngASEM = Range("AY3:AY1000")
For Each CellASEM In RngASEM
If CellASEM.Interior.Color = Grey Or CellASEM.Interior.Color = Green Or CellASEM.Interior.Color = LightBlue Or CellASEM.Value = "" Or CellASEM.Value = "NA" Then
CellASEM.EntireRow.Hidden = True
End If
Next CellASEM
'Sort by date
Range("A3:BY1000").Sort Key1:=Range("AY3"), Order1:=xlAscending
'Turn screen updating off for page break calculations because Excel is dumb
' Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/assemblysand.pdf", OpenAfterPublish:=True
'If a schedule is 2 pages or less, print all dates otherwise hide all rows past tomorrow's date (a) and then print the remaining data.
totalPages = Worksheets("Master Schedule").HPageBreaks.Count + 1
If totalPages <= 2 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/AssemblySand.pdf", OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(2).Location.Row, 51).Value > a Or totalPages = 3 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/AssemblySand.pdf", From:=1, To:=3, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(3).Location.Row, 51).Value > a Or totalPages = 4 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/AssemblySand.pdf", From:=1, To:=4, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(4).Location.Row, 51).Value > a Or totalPages = 5 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/AssemblySand.pdf", From:=1, To:=5, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(5).Location.Row, 51).Value > a Or totalPages = 6 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/AssemblySand.pdf", From:=1, To:=6, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(6).Location.Row, 51).Value > a Or totalPages = 7 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/AssemblySand.pdf", From:=1, To:=7, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(7).Location.Row, 51).Value > a Or totalPages = 8 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/AssemblySand.pdf", From:=1, To:=8, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(8).Location.Row, 51).Value > a Or totalPages = 9 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/AssemblySand.pdf", From:=1, To:=9, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(9).Location.Row, 51).Value > a Or totalPages = 10 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/AssemblySand.pdf", From:=1, To:=10, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(10).Location.Row, 51).Value > a Or totalPages = 11 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/AssemblySand.pdf", From:=1, To:=11, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(11).Location.Row, 51).Value > a Or totalPages = 12 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/AssemblySand.pdf", From:=1, To:=12, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(12).Location.Row, 51).Value > a Or totalPages = 13 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/AssemblySand.pdf", From:=1, To:=13, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(13).Location.Row, 51).Value > a Or totalPages = 14 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/AssemblySand.pdf", From:=1, To:=14, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(14).Location.Row, 51).Value > a Or totalPages = 15 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/AssemblySand.pdf", From:=1, To:=15, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(15).Location.Row, 51).Value > a Or totalPages = 16 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/AssemblySand.pdf", From:=1, To:=16, OpenAfterPublish:=True
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''' FINISH SCHEDULE '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Unhide all rows and hide BOM/OPT and Programming columns
Worksheets("Master Schedule").Rows.EntireRow.Hidden = False
Worksheets("Master Schedule").Range("P:BA").EntireColumn.Hidden = True
Worksheets("Master Schedule").Range("BB:BC").EntireColumn.Hidden = False
Worksheets("Master Schedule").Range("BD:BY").EntireColumn.Hidden = True
'Hide rows based on criteria
Dim CellFINH As Range
Dim RngFINH As Range
Set RngFINH = Range("BC3:BC1000")
For Each CellFINH In RngFINH
If CellFINH.Interior.Color = Grey Or CellFINH.Interior.Color = Green Or CellFINH.Interior.Color = LightBlue Or CellFINH.Value = "" Or CellFINH.Value = "NA" Then
CellFINH.EntireRow.Hidden = True
End If
Next CellFINH
'Sort by date
Range("A3:BY1000").Sort Key1:=Range("BC3"), Order1:=xlAscending
'Turn screen updating off for page break calculations because Excel is dumb
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Finish.pdf", OpenAfterPublish:=True
'If a schedule is 2 pages or less, print all dates otherwise hide all rows past tomorrow's date (a) and then print the remaining data.
' totalPages = Worksheets("Master Schedule").HPageBreaks.Count + 1
' If totalPages <= 2 Then
' Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Finish.pdf", OpenAfterPublish:=True
' ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(2).Location.Row, 54).Value > a Then
' Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Finish.pdf", From:=1, To:=2, OpenAfterPublish:=True
' ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(3).Location.Row, 54).Value > a Then
' Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Finish.pdf", From:=1, To:=3, OpenAfterPublish:=True
' ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(4).Location.Row, 54).Value > a Then
' Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Finish.pdf", From:=1, To:=4, OpenAfterPublish:=True
' ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(5).Location.Row, 54).Value > a Then
' Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Finish.pdf", From:=1, To:=5, OpenAfterPublish:=True
' ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(6).Location.Row, 54).Value > a Then
' Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Finish.pdf", From:=1, To:=6, OpenAfterPublish:=True
' ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(7).Location.Row, 54).Value > a Then
' Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Finish.pdf", From:=1, To:=7, OpenAfterPublish:=True
' ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(8).Location.Row, 54).Value > a Then
' Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Finish.pdf", From:=1, To:=8, OpenAfterPublish:=True
' ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(9).Location.Row, 54).Value > a Then
' Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Finish.pdf", From:=1, To:=9, OpenAfterPublish:=True
' ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(10).Location.Row, 54).Value > a Then
' Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Finish.pdf", From:=1, To:=10, OpenAfterPublish:=True
' End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''' PEDLINE SCHEDULE ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Unhide all rows and hide BOM/OPT and Programming columns
Worksheets("Master Schedule").Rows.EntireRow.Hidden = False
Worksheets("Master Schedule").Range("P:BE").EntireColumn.Hidden = True
Worksheets("Master Schedule").Range("BF:BG").EntireColumn.Hidden = False
Worksheets("Master Schedule").Range("BH:BY").EntireColumn.Hidden = True
'Hide rows based on criteria
Dim CellPEDL As Range
Dim RngPEDL As Range
Set RngPEDL = Range("BG3:BG1000")
For Each CellPEDL In RngPEDL
If CellPEDL.Interior.Color = Grey Or CellPEDL.Interior.Color = Green Or CellPEDL.Interior.Color = LightBlue Or CellPEDL.Value = "" Or CellPEDL.Value = "NA" Then
CellPEDL.EntireRow.Hidden = True
End If
Next CellPEDL
'Sort by date
Range("A3:BY1000").Sort Key1:=Range("BG3"), Order1:=xlAscending
'Turn screen updating off for page break calculations because Excel is dumb
'If a schedule is 2 pages or less, print all dates otherwise hide all rows past tomorrow's date (a) and then print the remaining data.
totalPages = Worksheets("Master Schedule").HPageBreaks.Count + 1
If totalPages <= 2 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pedline.pdf", OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(2).Location.Row, 59).Value > a Or totalPages = 3 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pedline.pdf", From:=1, To:=3, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(3).Location.Row, 59).Value > a Or totalPages = 4 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pedline.pdf", From:=1, To:=4, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(4).Location.Row, 59).Value > a Or totalPages = 5 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pedline.pdf", From:=1, To:=5, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(5).Location.Row, 59).Value > a Or totalPages = 6 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pedline.pdf", From:=1, To:=6, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(6).Location.Row, 59).Value > a Or totalPages = 7 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pedline.pdf", From:=1, To:=7, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(7).Location.Row, 59).Value > a Or totalPages = 8 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pedline.pdf", From:=1, To:=8, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(8).Location.Row, 59).Value > a Or totalPages = 9 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pedline.pdf", From:=1, To:=9, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(9).Location.Row, 59).Value > a Or totalPages = 10 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pedline.pdf", From:=1, To:=10, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(10).Location.Row, 59).Value > a Or totalPages = 11 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pedline.pdf", From:=1, To:=11, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(11).Location.Row, 59).Value > a Or totalPages = 12 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pedline.pdf", From:=1, To:=12, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(12).Location.Row, 59).Value > a Or totalPages = 13 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pedline.pdf", From:=1, To:=13, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(13).Location.Row, 59).Value > a Or totalPages = 14 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pedline.pdf", From:=1, To:=14, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(14).Location.Row, 59).Value > a Or totalPages = 15 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pedline.pdf", From:=1, To:=15, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(15).Location.Row, 59).Value > a Or totalPages = 16 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Pedline.pdf", From:=1, To:=16, OpenAfterPublish:=True
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''' HARDWARE SCHEDULE '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Unhide all rows and hide BOM/OPT and Programming columns
Worksheets("Master Schedule").Rows.EntireRow.Hidden = False
Worksheets("Master Schedule").Range("P:BI").EntireColumn.Hidden = True
Worksheets("Master Schedule").Range("BJ:BK").EntireColumn.Hidden = False
Worksheets("Master Schedule").Range("BL:BY").EntireColumn.Hidden = True
'Hide rows based on criteria
Dim CellHARD As Range
Dim RngHARD As Range
Set RngHARD = Range("BK3:BK1000")
For Each CellHARD In RngHARD
If CellHARD.Interior.Color = Grey Or CellHARD.Interior.Color = Green Or CellHARD.Interior.Color = LightBlue Or CellHARD.Value = "" Or CellHARD.Value = "NA" Then
CellHARD.EntireRow.Hidden = True
End If
Next CellHARD
'Sort by date
Range("A3:BY1000").Sort Key1:=Range("BK3"), Order1:=xlAscending
'Turn screen updating off for page break calculations because Excel is dumb
'If a schedule is 2 pages or less, print all dates otherwise hide all rows past tomorrow's date (a) and then print the remaining data.
totalPages = Worksheets("Master Schedule").HPageBreaks.Count + 1
If totalPages <= 2 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Hardware.pdf", OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(2).Location.Row, 63).Value > a Or totalPages = 3 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Hardware.pdf", From:=1, To:=3, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(3).Location.Row, 63).Value > a Or totalPages = 4 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Hardware.pdf", From:=1, To:=4, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(4).Location.Row, 63).Value > a Or totalPages = 5 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Hardware.pdf", From:=1, To:=5, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(5).Location.Row, 63).Value > a Or totalPages = 6 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Hardware.pdf", From:=1, To:=6, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(6).Location.Row, 63).Value > a Or totalPages = 7 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Hardware.pdf", From:=1, To:=7, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(7).Location.Row, 63).Value > a Or totalPages = 8 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Hardware.pdf", From:=1, To:=8, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(8).Location.Row, 63).Value > a Or totalPages = 9 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Hardware.pdf", From:=1, To:=9, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(9).Location.Row, 63).Value > a Or totalPages = 10 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Hardware.pdf", From:=1, To:=10, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(10).Location.Row, 63).Value > a Or totalPages = 11 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Hardware.pdf", From:=1, To:=11, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(11).Location.Row, 63).Value > a Or totalPages = 12 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Hardware.pdf", From:=1, To:=12, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(12).Location.Row, 63).Value > a Or totalPages = 13 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Hardware.pdf", From:=1, To:=13, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(13).Location.Row, 63).Value > a Or totalPages = 14 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Hardware.pdf", From:=1, To:=14, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(14).Location.Row, 63).Value > a Or totalPages = 15 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Hardware.pdf", From:=1, To:=15, OpenAfterPublish:=True
ElseIf Cells(Worksheets("Master Schedule").HPageBreaks(15).Location.Row, 63).Value > a Or totalPages = 16 Then
Worksheets("Master Schedule").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/Hardware.pdf", From:=1, To:=16, OpenAfterPublish:=True
End If
'Close application without saving
ThisWorkbook.Close Saved = True
End Sub
Last edited by a moderator: