G'day I have 1 issue.
The issue being I can run this code (indefinitely) which is awesome! it runs 7 days a week 24 hours a day on a never ending loop (we have shift workers and load up thousands of jobs a month).
The other code: (which has an issue runs for 4-5 hours then "seems like" it crashes basically hangs for an extended period.) I've coded both these myself so I know the code SHOULD be the same and doing the same thing however it's giving me grief.
The only substaintial difference is the first SUBs have 2 machines - this has 6. The second code runs (does more things) at a 20 x faster rate.
What I mean by this is JOBs complete every 1 minute - does the copy/bumps the jobs up etc. The first code does the routines every 15-20minutes.
So I'm putting this down to "Memory" but nothing is pointing at that.
Any outside eyes welcomed.. perhaps I'm too close to this. (Happy to give the original .XLSM to whomever to help troubleshoot at a moments notice also.
Luke
The issue being I can run this code (indefinitely) which is awesome! it runs 7 days a week 24 hours a day on a never ending loop (we have shift workers and load up thousands of jobs a month).
Code:
Public v As IntegerSub BeginAutomation()
Application.ScreenUpdating = False
v = 0
Dim Msg As String, Ans As Variant
Msg = "You're about to begin automation on the C Section Machines do you wish to proeceed?"
Ans = MsgBox(Msg, vbYesNo)
Select Case Ans
Case vbYes
Call TimerControl
Case vbNo
GoTo quit:
End Select
With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
quit:
Application.ScreenUpdating = True
End Sub
Sub STOPAUTOMATION()
Range("XFC8").Value = "1"
ActiveWorkbook.Save
If Range("XFC8").Value = "1" Then Call EXITAUTO
End Sub
Sub TimerControl()
If v = 0 Then
TimeToRun = Now + TimeValue("00:00:10")
Application.OnTime TimeToRun, "LoadC15015"
Else
Exit Sub
End If
End Sub
Sub LoadC15015()
'Set WshNetwork = CreateObject("wscript.network")
Application.ScreenUpdating = False
Dim r As Long, ws As Worksheet
Dim wb As Workbook
Set wb = Workbooks("Best Shed Scheduler C Section.xlsm")
Dim ws2 As Worksheet
Dim WshNetwork
If Sheets("C15015 Machine Batch").Range("P3").Value = "1" And Sheets("C15015 Machine Batch").Range("R3").Value = "0" Then
Sheets("C15015 Machine Batch").Range("R3") = "2"
'Sheets("C15015 Machine Batch").Range("AP3") = "1"
Sheets("C15015 Machine Batch").Range("AO3").Value = Now
ActiveWorkbook.Save
Call movecompletedc15015
End If
''Run the Load''
If Sheets("C15015 Machine Batch").Range("P3").Value = "1" And Sheets("C15015 Machine Batch").Range("R3").Value = "4" Then
If Sheets("C15015 Machine Data").Range("A3") >= 1 Then
Sheets("C15015 Machine Batch").Range("R3") = "1"
''Load next job
ThisWorkbook.Sheets("C15015 Machine Data").Range("B6").Value = ThisWorkbook.Sheets("C15015 Machine Data").Range("A3").Value ''AS B2 in Data sheet is always going to be current First in First out concept
For i = 3 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("C15015 Machine Data").Range("A" & i).Value = ThisWorkbook.Sheets("C15015 Machine Data").Range("B6").Value Then
Exit For
End If
Next i
Sheets("C15015 Machine Data").Range("A3:M" & i - 1).Copy
Sheets("C15015 Machine Batch").Cells(3, 1).PasteSpecial Paste:=xlValues
Sheets("C15015 Machine Batch").Range("AN3").Value = Now
Set ws = Sheets("C15015 Label")
Set WshNetwork = CreateObject("WScript.Network")
WshNetwork.SetDefaultPrinter "C15015 Printer"
Sheets("C15015 Label").PrintOut ActivePrinter:="C15015 Printer"
'''Remove data from the original DATA Sheet from Downpipe Machine Sheet where the Batch originated from
ThisWorkbook.Sheets("C15015 Machine Data").Rows("3:" & i - 1).Delete
Set ws = Sheets("C15015 Machine Batch")
For r = 4 To 14
If ws.Range("F" & r).Value = "" Then ws.Range("F" & r & ":M" & r) = "0"
Next r
'''JOB HAS LOADED SEND TO GRANT
Sheets("C15015 Machine Batch").Range("Q3") = "1"
End If
End If
'''Grant's magic
If Sheets("C15015 Machine Batch").Range("S3") = "1" Then
Sheets("C15015 Machine Batch").Range("R3") = "0"
Sheets("C15015 Machine Batch").Range("Q3") = "0"
End If
Call LoadC15024
Application.ScreenUpdating = True
End Sub
Sub movecompletedc15015()
Application.ScreenUpdating = False
Dim ws As Worksheet, Lastrow As Long
Dim ws2 As Worksheet
Set ws = Sheets("C15015 Machine Batch")
''Move the job to daily recorded sheet
'ThisWorkbook.Sheets("Downpipe Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Downpipe Machine").Range("B6").Value ''AS B2 in Data sheet is always going to be current First in First out concept
For i = 4 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("C15015 Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("C15015 Machine Batch").Range("A3").Value Then
Exit For
End If
Next i
ws.Range("A3:AO" & i - 1).Copy
Set ws2 = Sheets("C15015 Jobs")
Lastrow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row + 1
Sheets("C15015 Jobs").Range("A" & Lastrow).PasteSpecial Paste:=xlPasteValues
Call RecordC15015
Application.ScreenUpdating = True
End Sub
Sub RecordC15015()
Dim ws As Worksheet
Set ws = Sheets("C15015 Machine Batch")
With ws
For i = 4 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("C15015 Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("C15015 Machine Batch").Range("A3").Value Then
Exit For
End If
Next i
.Range("A3:AO" & i - 1).ClearContents
.Range("R3") = 4
.Range("AQ3") = 0
End With
Call LoadC15015
End Sub
Sub LoadC15024()
Application.ScreenUpdating = False
Dim r As Long, ws As Worksheet
Dim wb As Workbook
Set wb = Workbooks("Best Shed Scheduler C Section.xlsm")
Dim WshNetwork
'''COMPLETED JOB SECTION
If Sheets("C15024 Machine Batch").Range("P3").Value = "1" And Sheets("C15024 Machine Batch").Range("R3").Value = "0" Then
Sheets("C15024 Machine Batch").Range("R3") = "2"
Sheets("C15024 Machine Batch").Range("AO3").Value = Now
ActiveWorkbook.Save
Call movecompletec15024
End If
''Run the Load''
If Sheets("C15024 Machine Batch").Range("P3").Value = "1" And Sheets("C15024 Machine Batch").Range("R3").Value = "4" Then
If Sheets("C15024 Machine Data").Range("A3") >= 1 Then
Sheets("C15024 Machine Batch").Range("R3") = "1"
''Load next job
ThisWorkbook.Sheets("C15024 Machine Data").Range("B6").Value = ThisWorkbook.Sheets("C15024 Machine Data").Range("A3").Value ''AS B2 in Data sheet is always going to be current First in First out concept
For i = 3 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("C15024 Machine Data").Range("A" & i).Value = ThisWorkbook.Sheets("C15024 Machine Data").Range("B6").Value Then
Exit For
End If
Next i
Sheets("C15024 Machine Data").Range("A3:M" & i - 1).Copy
Sheets("C15024 Machine Batch").Cells(3, 1).PasteSpecial Paste:=xlValues
Sheets("C15024 Machine Batch").Range("AN3").Value = Now
Set ws = Sheets("C15024 Label")
Set WshNetwork = CreateObject("WScript.Network")
WshNetwork.SetDefaultPrinter "C15024 Printer"
Sheets("C15024 Label").PrintOut ActivePrinter:="C15024 Printer"
Set ws = Sheets("C15024 Machine Batch")
For r = 4 To 14
If ws.Range("F" & r).Value = "" Then ws.Range("F" & r & ":M" & r) = "0"
Next r
'''Remove data from the original DATA Sheet from C15024 Machine Data Sheet where the Batch originated from
ThisWorkbook.Sheets("C15024 Machine Data").Rows("3:" & i - 1).Delete ''delete the data that you are currently using from main table
'Job Loaded - Tell Grant - Trigger Grant's Magic
Sheets("C15024 Machine Batch").Range("Q3") = "1"
End If
End If
'''Grant's magic
If Sheets("C15024 Machine Batch").Range("S3") = "1" Then
Sheets("C15024 Machine Batch").Range("R3") = "0"
Sheets("C15024 Machine Batch").Range("Q3") = "0"
'ActiveSheet.Range("AQ3") = "0"
End If
Call CheckCoilC15024
Application.ScreenUpdating = True
End Sub
Sub movecompletec15024()
Application.ScreenUpdating = False
Dim ws As Worksheet, Lastrow As Long
Dim ws2 As Worksheet
Set ws = Sheets("C15024 Machine Batch")
''Move the job to daily recorded sheet
'ThisWorkbook.Sheets("Downpipe Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Downpipe Machine").Range("B6").Value ''AS B2 in Data sheet is always going to be current First in First out concept
For i = 4 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("C15024 Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("C15024 Machine Batch").Range("A3").Value Then
Exit For
End If
Next i
ws.Range("A3:AO" & i - 1).Copy
Set ws2 = Sheets("C15024 Jobs")
Lastrow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row + 1
Sheets("C15024 Jobs").Range("A" & Lastrow).PasteSpecial Paste:=xlPasteValues
Application.ScreenUpdating = True
Call ClearC15024Batch
End Sub
Sub ClearC15024Batch()
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = Sheets("C15024 Machine Batch")
With ws
For i = 4 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("C15024 Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("C15024 Machine Batch").Range("A3").Value Then
Exit For
End If
Next i
.Range("A3:AO" & i - 1).ClearContents
.Range("R3") = 4
.Range("AP3") = 0 'Printer new field
End With
Call LoadC15024
Application.ScreenUpdating = True
End Sub
Sub CheckCoilC15024()
Application.ScreenUpdating = False
Dim ws As Worksheet, Lastrow As Long
Dim ws2 As Worksheet
Set ws = Sheets("C15024 Machine Batch")
If Sheets("C15024 Machine Batch").Range("AQ3") = 1 Then
Sheets("C15024 Machine Batch").Range("AT3:BC28").Copy
Set ws2 = Sheets("C15024 Coil")
Lastrow = ws2.Cells(ws2.Rows.Count, "AT").End(xlUp).Row + 1
Sheets("C15024 Coil").Range("AT" & Lastrow).PasteSpecial Paste:=xlPasteValues
End If
Call CheckCoilC15015
Application.ScreenUpdating = True
End Sub
Sub CheckCoilC15015()
Application.ScreenUpdating = False
Dim ws As Worksheet, Lastrow As Long
Dim ws2 As Worksheet
Set ws = Sheets("C15015 Machine Batch")
If Sheets("C15015 Machine Batch").Range("AQ3") = 1 Then
Sheets("C15015 Machine Batch").Range("AT3:BC28").Copy
Set ws2 = Sheets("C15015 Coil")
Lastrow = ws2.Cells(ws2.Rows.Count, "AT").End(xlUp).Row + 1
Sheets("C15015 Coil").Range("AT" & Lastrow).PasteSpecial Paste:=xlPasteValues
End If
Call TimerControl
Application.ScreenUpdating = True
End SubThe other code: (which has an issue runs for 4-5 hours then "seems like" it crashes basically hangs for an extended period.) I've coded both these myself so I know the code SHOULD be the same and doing the same thing however it's giving me grief.
The only substaintial difference is the first SUBs have 2 machines - this has 6. The second code runs (does more things) at a 20 x faster rate.
What I mean by this is JOBs complete every 1 minute - does the copy/bumps the jobs up etc. The first code does the routines every 15-20minutes.
So I'm putting this down to "Memory" but nothing is pointing at that.
Code:
Public v As IntegerSub BeginAutomation()
Application.ScreenUpdating = False
v = 0
Dim Msg As String, Ans As Variant
Msg = "You're about to begin automation do you wish to proceed?"
Ans = MsgBox(Msg, vbYesNo)
Select Case Ans
Case vbYes
Call Timercontrol
Case vbNo
GoTo Quit:
End Select
Application.ScreenUpdating = True
Quit:
End Sub
Sub STOPAUTOMATION()
Application.ScreenUpdating = False
Range("XFC8").Value = "1"
ActiveWorkbook.Save
If Range("XFC8").Value = "1" Then Call EXITAUTO
Exit Sub
Application.ScreenUpdating = True
End Sub
Sub Timercontrol()
Application.ScreenUpdating = False
If v = 0 Then
TimeToRun = Now + TimeValue("00:00:09")
Application.OnTime TimeToRun, "LoadDownpipe"
Else
Exit Sub
End If
Application.ScreenUpdating = True
End Sub
Sub LoadDownpipe()
Application.ScreenUpdating = False
Dim r As Long, ws As Worksheet
Dim wb As Workbook
Set wb = Workbooks("Best Shed Scheduler Flashing.xlsm")
If Sheets("Downpipe Machine Batch").Range("N3") = 3 And Sheets("Downpipe Machine Batch").Range("P3").Value = 1 And Sheets("Downpipe Machine Batch").Range("AJ3") = 0 Then
Sheets("Downpipe Machine Batch").Range("AJ3") = 2
'''Job has been completed TIME STAMP
Sheets("Downpipe Machine Batch").Range("AN3").Value = Now
''Engage the move of completed downpipe batch once completed triggers are found
Call movecompletedDownpipe
End If
''Run the Load''
If Sheets("Downpipe Machine Batch").Range("N3") = 3 And Sheets("Downpipe Machine Batch").Range("P3").Value = 1 And Sheets("Downpipe Machine Batch").Range("AJ3").Value = 4 Then
'Any more jobs to load?
If Sheets("Downpipe Machine Data").Range("A3") >= 1 Then
Sheets("Downpipe Machine Batch").Range("AJ3") = "1"
''Load next job
ThisWorkbook.Sheets("Downpipe Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Downpipe Machine Data").Range("A3").Value ''AS B2 in Data sheet is always going to be current First in First out concept
For i = 3 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Downpipe Machine Data").Range("A" & i).Value = ThisWorkbook.Sheets("Downpipe Machine Batch").Range("B6").Value Then
Exit For
End If
Next i
'''Need help with this section getting rid of selects - need it to jump between sheets but can't work out how to do it
Sheets("Downpipe Machine Data").Range("A3:Z" & i - 1).Copy
Sheets("Downpipe Machine Batch").Cells(3, 1).PasteSpecial Paste:=xlValues
''''TIME STAMP LOAD IN
Sheets("Downpipe Machine Batch").Range("AM3").Value = Now
'''Remove data from the original DATA Sheet from Downpipe Machine Sheet where the Batch originated from
ThisWorkbook.Sheets("Downpipe Machine Data").Rows("3:" & i - 1).Delete
'''WIRTE Zeros if No QTY/Length is present to enforce machine register START
Set ws = Sheets("Downpipe Machine Batch")
For r = 4 To 14
If ws.Range("F" & r).Value = "" Then ws.Range("F" & r & ":G" & r) = "0"
Next r
'''Tell the machine the job has been loaded
Sheets("Downpipe Machine Batch").Range("R3") = "1"
'Application.Wait (Now + TimeValue("0:00:05"))
End If
End If
'''New code - test first
If Sheets("Downpipe Machine Batch").Range("AK3") = 1 Then
Sheets("Downpipe Machine Batch").Range("R3") = 0
Sheets("Downpipe Machine Batch").Range("AJ3") = 0
End If
Call LoadGutter
Application.ScreenUpdating = True
End Sub
Sub movecompletedDownpipe()
Application.ScreenUpdating = False
Dim ws As Worksheet, Lastrow As Long
Dim ws2 As Worksheet
Set ws = Sheets("Downpipe Machine Batch")
''Move the job to daily recorded sheet
'ThisWorkbook.Sheets("Downpipe Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Downpipe Machine").Range("B6").Value ''AS B2 in Data sheet is always going to be current First in First out concept
For i = 4 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Downpipe Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Downpipe Machine Batch").Range("A3").Value Then
Exit For
End If
Next i
ws.Range("A3:AN" & i - 1).Copy
Set ws2 = Sheets("Downpipe Job List")
Lastrow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row + 1
Sheets("Downpipe Job List").Range("A" & Lastrow).PasteSpecial Paste:=xlPasteValues
'Sheets("Downpipe").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).PasteSpecial Paste:=xlPasteValues
Call ClearDownpipeBatch
Application.ScreenUpdating = True
End Sub
Sub ClearDownpipeBatch()
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = Sheets("Downpipe Machine Batch")
With ws
For i = 4 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Downpipe Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Downpipe Machine Batch").Range("A3").Value Then
Exit For
End If
Next i
.Range("A3:CC" & i - 1).ClearContents
.Range("AJ3") = 4
End With
Call LoadDownpipe
Application.ScreenUpdating = True
End Sub
Sub LoadGutter()
Application.ScreenUpdating = False
Dim r As Long, ws As Worksheet
Dim wb As Workbook
Set wb = Workbooks("Best Shed Scheduler Flashing.xlsm")
'If Sheets("Gutter Machine Batch").Range("R3") = 1 And Sheets("Gutter Machine Batch").Range("AJ3") = 1 And Sheets("Gutter Machine Batch").Range("AK3") = 0 Then
'Sheets("Gutter Machine Batch").Range("R3") = 0
'Sheets("Gutter Machine Batch").Range("AJ3") = 0
'End If
If Sheets("Gutter Machine Batch").Range("N3") = 3 And Sheets("Gutter Machine Batch").Range("P3").Value = 1 And Sheets("Gutter Machine Batch").Range("AJ3") = 0 Then
Sheets("Gutter Machine Batch").Range("AJ3") = 2
Sheets("Gutter Machine Batch").Range("AN3").Value = Now
Call movecompletedGutter
End If
''Run the Load''
If Sheets("Gutter Machine Batch").Range("N3") = 3 And Sheets("Gutter Machine Batch").Range("P3").Value = 1 And Sheets("Gutter Machine Batch").Range("AJ3").Value = 4 Then
'NEW SHORT CODE
If Sheets("Gutter Machine Data").Range("A3") >= 1 Then
Sheets("Gutter Machine Batch").Range("AJ3") = "1"
''Load next job
ThisWorkbook.Sheets("Gutter Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Gutter Machine Data").Range("A3").Value ''AS B2 in Data sheet is always going to be current First in First out concept
For i = 3 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Gutter Machine Data").Range("A" & i).Value = ThisWorkbook.Sheets("Gutter Machine Batch").Range("B6").Value Then
Exit For
End If
Next i
'''Need help with this section getting rid of selects - need it to jump between sheets but can't work out how to do it
Sheets("Gutter Machine Data").Range("A3:Z" & i - 1).Copy
Sheets("Gutter Machine Batch").Cells(3, 1).PasteSpecial Paste:=xlValues
''''TIME STAMP LOAD IN
Sheets("Gutter Machine Batch").Range("AM3").Value = Now
'''Remove data from the original DATA Sheet from Gutter Machine Sheet where the Batch originated from
ThisWorkbook.Sheets("Gutter Machine Data").Rows("3:" & i - 1).Delete
'''WIRTE Zeros if No QTY/Length is present to enforce machine register START
Set ws = Sheets("Gutter Machine Batch")
For r = 4 To 14
If ws.Range("F" & r).Value = "" Then ws.Range("F" & r & ":G" & r) = "0"
Next r
'''Tell the machine the job has been loaded
Sheets("Gutter Machine Batch").Range("R3") = "1"
'Application.Wait (Now + TimeValue("0:00:05"))
End If
End If
'''MAGIC
If Sheets("Gutter Machine Batch").Range("AK3") = 1 Then
Sheets("Gutter Machine Batch").Range("R3") = 0
Sheets("Gutter Machine Batch").Range("AJ3") = 0
End If
Call LoadBarge
Application.ScreenUpdating = True
End Sub
Sub movecompletedGutter()
Application.ScreenUpdating = False
Dim ws As Worksheet, Lastrow As Long
Dim ws2 As Worksheet
Set ws = Sheets("Gutter Machine Batch")
''Move the job to daily recorded sheet
'ThisWorkbook.Sheets("Gutter Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Gutter Machine").Range("B6").Value ''AS B2 in Data sheet is always going to be current First in First out concept
For i = 4 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Gutter Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Gutter Machine Batch").Range("A3").Value Then
Exit For
End If
Next i
ws.Range("A3:AN" & i - 1).Copy
Set ws2 = Sheets("Gutter Job List")
Lastrow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row + 1
'Coil Sheet
Sheets("Gutter Job List").Range("A" & Lastrow).PasteSpecial Paste:=xlPasteValues
'Sheets("Gutter").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).PasteSpecial Paste:=xlPasteValues
Call ClearGutterBatch
Application.ScreenUpdating = True
End Sub
Sub ClearGutterBatch()
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = Sheets("Gutter Machine Batch")
With ws
For i = 4 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Gutter Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Gutter Machine Batch").Range("A3").Value Then
Exit For
End If
Next i
.Range("A3:CC" & i - 1).ClearContents
.Range("AJ3") = 4
End With
Call LoadGutter
Application.ScreenUpdating = True
End Sub
Sub LoadBarge()
Application.ScreenUpdating = False
Dim r As Long, ws As Worksheet
Dim wb As Workbook
Set wb = Workbooks("Best Shed Scheduler Flashing.xlsm")
'If Sheets("Barge Machine Batch").Range("R3") = 1 And Sheets("Barge Machine Batch").Range("AJ3") = 1 And Sheets("Barge Machine Batch").Range("AK3") = 0 Then
'Sheets("Barge Machine Batch").Range("R3") = 0
'Sheets("Barge Machine Batch").Range("AJ3") = 0
'End If
If Sheets("Barge Machine Batch").Range("N3") = 3 And Sheets("Barge Machine Batch").Range("P3").Value = 1 And Sheets("Barge Machine Batch").Range("AJ3") = 0 Then
Sheets("Barge Machine Batch").Range("AJ3") = 2
'''Job has been completed TIME STAMP
Sheets("Barge Machine Batch").Range("AN3").Value = Now
''Engage the move of completed Barge batch once completed triggers are found
Call movecompletedBarge
End If
''Run the Load''
If Sheets("Barge Machine Batch").Range("N3") = 3 And Sheets("Barge Machine Batch").Range("P3").Value = 1 And Sheets("Barge Machine Batch").Range("AJ3").Value = 4 Then
'If ActiveSheet.Range("N3").Value = "3" And ActiveSheet.Range("P3").Value = "1" And ActiveSheet.Range("AJ3") = "4" Then
'Sheets("Barge Machine Data").Select
'NEW SHORT CODE
If Sheets("Barge Machine Data").Range("A3") >= 1 Then
Sheets("Barge Machine Batch").Range("AJ3") = "1"
''Load next job
ThisWorkbook.Sheets("Barge Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Barge Machine Data").Range("A3").Value ''AS B2 in Data sheet is always going to be current First in First out concept
For i = 3 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Barge Machine Data").Range("A" & i).Value = ThisWorkbook.Sheets("Barge Machine Batch").Range("B6").Value Then
Exit For
End If
Next i
'''Need help with this section getting rid of selects - need it to jump between sheets but can't work out how to do it
Sheets("Barge Machine Data").Range("A3:Z" & i - 1).Copy
Sheets("Barge Machine Batch").Cells(3, 1).PasteSpecial Paste:=xlValues
''''TIME STAMP LOAD IN
Sheets("Barge Machine Batch").Range("AM3").Value = Now
'''Remove data from the original DATA Sheet from Barge Machine Sheet where the Batch originated from
ThisWorkbook.Sheets("Barge Machine Data").Rows("3:" & i - 1).Delete
'Workbook SAVE on LOAD JOB
'wb.Save
'''WIRTE Zeros if No QTY/Length is present to enforce machine register START
Set ws = Sheets("Barge Machine Batch")
For r = 4 To 14
If ws.Range("F" & r).Value = "" Then ws.Range("F" & r & ":G" & r) = "0"
Next r
'''Tell the machine the job has been loaded
Sheets("Barge Machine Batch").Range("R3") = "1"
'Application.Wait (Now + TimeValue("0:00:05"))
End If
End If
'''New code - test first
If Sheets("Barge Machine Batch").Range("AK3") = 1 Then
Sheets("Barge Machine Batch").Range("R3") = 0
Sheets("Barge Machine Batch").Range("AJ3") = 0
End If
Call LoadCornerFlashing
Application.ScreenUpdating = True
End Sub
Sub movecompletedBarge()
Application.ScreenUpdating = False
Dim ws As Worksheet, Lastrow As Long
Dim ws2 As Worksheet
Set ws = Sheets("Barge Machine Batch")
''Move the job to daily recorded sheet
'ThisWorkbook.Sheets("Barge Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Barge Machine").Range("B6").Value ''AS B2 in Data sheet is always going to be current First in First out concept
For i = 4 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Barge Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Barge Machine Batch").Range("A3").Value Then
Exit For
End If
Next i
ws.Range("A3:AN" & i - 1).Copy
Set ws2 = Sheets("Barge Job List")
Lastrow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row + 1
Sheets("Barge Job List").Range("A" & Lastrow).PasteSpecial Paste:=xlPasteValues
'Sheets("Barge").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).PasteSpecial Paste:=xlPasteValues
Call ClearBargeBatch
Application.ScreenUpdating = True
End Sub
Sub ClearBargeBatch()
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = Sheets("Barge Machine Batch")
With ws
For i = 4 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Barge Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Barge Machine Batch").Range("A3").Value Then
Exit For
End If
Next i
.Range("A3:CC" & i - 1).ClearContents
.Range("AJ3") = 4
End With
Call LoadBarge
Application.ScreenUpdating = True
End Sub
Sub LoadCornerFlashing()
Application.ScreenUpdating = False
Dim r As Long, ws As Worksheet
Dim wb As Workbook
Set wb = Workbooks("Best Shed Scheduler Flashing.xlsm")
Dim WshNetwork
'If Sheets("Corner Flashing Machine Batch").Range("R3") = 1 And Sheets("Corner Flashing Machine Batch").Range("AJ3") = 1 And Sheets("Corner Flashing Machine Batch").Range("AK3") = 0 Then
'Sheets("Corner Flashing Machine Batch").Range("R3") = 0
'Sheets("Corner Flashing Machine Batch").Range("AJ3") = 0
'End If
If Sheets("Corner Flashing Machine Batch").Range("N3") = 3 And Sheets("Corner Flashing Machine Batch").Range("P3").Value = 1 And Sheets("Corner Flashing Machine Batch").Range("AJ3") = 0 Then
Sheets("Corner Flashing Machine Batch").Range("AJ3") = 2
'''Job has been completed TIME STAMP
Sheets("Corner Flashing Machine Batch").Range("AN3").Value = Now
''Engage the move of completed Corner Flashing batch once completed triggers are found
Call movecompletedcornerflashing
End If
''Run the Load''
If Sheets("Corner Flashing Machine Batch").Range("N3") = 3 And Sheets("Corner Flashing Machine Batch").Range("P3").Value = 1 And Sheets("Corner Flashing Machine Batch").Range("AJ3").Value = 4 Then
'NEW SHORT CODE
If Sheets("Corner Flashing Machine Data").Range("A3") >= 1 Then
Sheets("Corner Flashing Machine Batch").Range("AJ3") = "1"
''Load next job
ThisWorkbook.Sheets("Corner Flashing Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Corner Flashing Machine Data").Range("A3").Value ''AS B2 in Data sheet is always going to be current First in First out concept
For i = 3 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Corner Flashing Machine Data").Range("A" & i).Value = ThisWorkbook.Sheets("Corner Flashing Machine Batch").Range("B6").Value Then
Exit For
End If
Next i
'''Need help with this section getting rid of selects - need it to jump between sheets but can't work out how to do it
Sheets("Corner Flashing Machine Data").Range("A3:Z" & i - 1).Copy
Sheets("Corner Flashing Machine Batch").Cells(3, 1).PasteSpecial Paste:=xlValues
''''TIME STAMP LOAD IN
Sheets("Corner Flashing Machine Batch").Range("AM3").Value = Now
''''Print Label
Set ws = Sheets("Corner Label")
Set WshNetwork = CreateObject("WScript.Network")
Sheets("Corner Label").PrintOut ActivePrinter:="Corner Flashing Label"
'''Remove data from the original DATA Sheet from Corner Flashing Machine Sheet where the Batch originated from
ThisWorkbook.Sheets("Corner Flashing Machine Data").Rows("3:" & i - 1).Delete
'''WIRTE Zeros if No QTY/Length is present to enforce machine register START
Set ws = Sheets("Corner Flashing Machine Batch")
For r = 4 To 14
If ws.Range("F" & r).Value = "" Then ws.Range("F" & r & ":G" & r) = "0"
Next r
'''Tell the machine the job has been loaded
Sheets("Corner Flashing Machine Batch").Range("R3") = "1"
'Application.Wait (Now + TimeValue("0:00:05"))
End If
End If
'''New code - test first
If Sheets("Corner Flashing Machine Batch").Range("AK3") = 1 Then
Sheets("Corner Flashing Machine Batch").Range("R3") = 0
Sheets("Corner Flashing Machine Batch").Range("AJ3") = 0
End If
Call LoadRidge300
Application.ScreenUpdating = True
End Sub
Sub movecompletedcornerflashing()
Application.ScreenUpdating = False
Dim ws As Worksheet, Lastrow As Long
Dim ws2 As Worksheet
Set ws = Sheets("Corner Flashing Machine Batch")
''Move the job to daily recorded sheet
'ThisWorkbook.Sheets("Corner FlashingMachine Batch").Range("B6").Value = ThisWorkbook.Sheets("Corner FlashingMachine").Range("B6").Value ''AS B2 in Data sheet is always going to be current First in First out concept
For i = 4 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Corner Flashing Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Corner Flashing Machine Batch").Range("A3").Value Then
Exit For
End If
Next i
ws.Range("A3:AN" & i - 1).Copy
Set ws2 = Sheets("Corner Flashing Job List")
Lastrow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row + 1
Sheets("Corner Flashing Job List").Range("A" & Lastrow).PasteSpecial Paste:=xlPasteValues
'Sheets("Gutter").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).PasteSpecial Paste:=xlPasteValues
Call ClearCornerFlashingBatch
Application.ScreenUpdating = True
End Sub
Sub ClearCornerFlashingBatch()
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = Sheets("Corner Flashing Machine Batch")
With ws
For i = 4 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Corner Flashing Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Corner Flashing Machine Batch").Range("A3").Value Then
Exit For
End If
Next i
.Range("A3:CC" & i - 1).ClearContents
.Range("AJ3") = 4
End With
Call LoadCornerFlashing
Application.ScreenUpdating = True
End Sub
Sub LoadRidge300()
Application.ScreenUpdating = False
Dim r As Long, ws As Worksheet
Dim wb As Workbook
Set wb = Workbooks("Best Shed Scheduler Flashing.xlsm")
Dim WshNetwork
'If Sheets("Ridge 300 Machine Batch").Range("R3") = 1 And Sheets("Ridge 300 Machine Batch").Range("AJ3") = 1 And Sheets("Ridge 300 Machine Batch").Range("AK3") = 0 Then
'Sheets("Ridge 300 Machine Batch").Range("R3") = 0
'Sheets("Ridge 300 Machine Batch").Range("AJ3") = 0
'End If
If Sheets("Ridge 300 Machine Batch").Range("N3") = 3 And Sheets("Ridge 300 Machine Batch").Range("P3").Value = 1 And Sheets("Ridge 300 Machine Batch").Range("AJ3") = 0 Then
Sheets("Ridge 300 Machine Batch").Range("AJ3") = 2
ActiveWorkbook.Save
'''Job has been completed TIME STAMP
Sheets("Ridge 300 Machine Batch").Range("AN3").Value = Now
''Engage the move of completed Ridge 300 batch once completed triggers are found
Call movecompletedridge300
End If
''Run the Load''
If Sheets("Ridge 300 Machine Batch").Range("N3") = 3 And Sheets("Ridge 300 Machine Batch").Range("P3").Value = 1 And Sheets("Ridge 300 Machine Batch").Range("AJ3").Value = 4 Then
'NEW SHORT CODE
If Sheets("Ridge 300 Machine Data").Range("A3") >= 1 Then
Sheets("Ridge 300 Machine Batch").Range("AJ3") = "1"
''Load next job
ThisWorkbook.Sheets("Ridge 300 Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Ridge 300 Machine Data").Range("A3").Value ''AS B2 in Data sheet is always going to be current First in First out concept
For i = 3 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Ridge 300 Machine Data").Range("A" & i).Value = ThisWorkbook.Sheets("Ridge 300 Machine Batch").Range("B6").Value Then
Exit For
End If
Next i
'''Need help with this section getting rid of selects - need it to jump between sheets but can't work out how to do it
Sheets("Ridge 300 Machine Data").Range("A3:Z" & i - 1).Copy
Sheets("Ridge 300 Machine Batch").Cells(3, 1).PasteSpecial Paste:=xlValues
''''TIME STAMP LOAD IN
Sheets("Ridge 300 Machine Batch").Range("AM3").Value = Now
'''Remove data from the original DATA Sheet from Ridge 300 Machine Sheet where the Batch originated from
ThisWorkbook.Sheets("Ridge 300 Machine Data").Rows("3:" & i - 1).Delete
'Print Label
Set ws = Sheets("Ridge300 Label")
Set WshNetwork = CreateObject("WScript.Network")
Sheets("Ridge300 Label").PrintOut ActivePrinter:="Ridge300 Label"
'''WIRTE Zeros if No QTY/Length is present to enforce machine register START
Set ws = Sheets("Ridge 300 Machine Batch")
For r = 4 To 14
If ws.Range("F" & r).Value = "" Then ws.Range("F" & r & ":G" & r) = "0"
Next r
'''Tell the machine the job has been loaded
Sheets("Ridge 300 Machine Batch").Range("R3") = "1"
'Application.Wait (Now + TimeValue("0:00:05"))
End If
End If
'''New code - test first
If Sheets("Ridge 300 Machine Batch").Range("AK3") = 1 Then
Sheets("Ridge 300 Machine Batch").Range("R3") = 0
Sheets("Ridge 300 Machine Batch").Range("AJ3") = 0
End If
Call LoadRidge400
Application.ScreenUpdating = True
End Sub
Sub movecompletedridge300()
Application.ScreenUpdating = False
Dim ws As Worksheet, Lastrow As Long
Dim ws2 As Worksheet
Set ws = Sheets("Ridge 300 Machine Batch")
''Move the job to daily recorded sheet
'ThisWorkbook.Sheets("Ridge 300Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Ridge 300Machine").Range("B6").Value ''AS B2 in Data sheet is always going to be current First in First out concept
For i = 4 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Ridge 300 Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Ridge 300 Machine Batch").Range("A3").Value Then
Exit For
End If
Next i
ws.Range("A3:AN" & i - 1).Copy
Set ws2 = Sheets("Ridge300 Job List")
Lastrow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row + 1
Sheets("Ridge300 Job List").Range("A" & Lastrow).PasteSpecial Paste:=xlPasteValues
'Sheets("Gutter").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).PasteSpecial Paste:=xlPasteValues
Call ClearRidge300
Application.ScreenUpdating = True
End Sub
Sub ClearRidge300()
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = Sheets("Ridge 300 Machine Batch")
With ws
For i = 4 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Ridge 300 Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Ridge 300 Machine Batch").Range("A3").Value Then
Exit For
End If
Next i
.Range("A3:CC" & i - 1).ClearContents
.Range("AJ3") = 4
End With
Call LoadRidge300
Application.ScreenUpdating = True
End Sub
Sub LoadRidge400()
Application.ScreenUpdating = False
Dim WshNetwork
Dim r As Long, ws As Worksheet
Dim wb As Workbook
Set wb = Workbooks("Best Shed Scheduler Flashing.xlsm")
'If Sheets("Ridge 400 Machine Batch").Range("R3") = 1 And Sheets("Ridge 400 Machine Batch").Range("AJ3") = 1 And Sheets("Ridge 400 Machine Batch").Range("AK3") = 0 Then
'Sheets("Ridge 400 Machine Batch").Range("R3") = 0
'Sheets("Ridge 400 Machine Batch").Range("AJ3") = 0
'End If
If Sheets("Ridge 400 Machine Batch").Range("N3") = 3 And Sheets("Ridge 400 Machine Batch").Range("P3").Value = 1 And Sheets("Ridge 400 Machine Batch").Range("AJ3") = 0 Then
Sheets("Ridge 400 Machine Batch").Range("AJ3") = 2
ActiveWorkbook.Save
'''Job has been completed TIME STAMP
Range("AN3").Value = Now
''Engage the move of completed Ridge 400 batch once completed triggers are found
Call movecompletedRidge400
End If
''Run the Load''
If Sheets("Ridge 400 Machine Batch").Range("N3") = 3 And Sheets("Ridge 400 Machine Batch").Range("P3").Value = 1 And Sheets("Ridge 400 Machine Batch").Range("AJ3").Value = 4 Then
'NEW SHORT CODE
If Sheets("Ridge 400 Machine Data").Range("A3") >= 1 Then
Sheets("Ridge 400 Machine Batch").Range("AJ3") = "1"
''Load next job
ThisWorkbook.Sheets("Ridge 400 Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Ridge 400 Machine Data").Range("A3").Value ''AS B2 in Data sheet is always going to be current First in First out concept
For i = 3 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Ridge 400 Machine Data").Range("A" & i).Value = ThisWorkbook.Sheets("Ridge 400 Machine Batch").Range("B6").Value Then
Exit For
End If
Next i
'''Need help with this section getting rid of selects - need it to jump between sheets but can't work out how to do it
Sheets("Ridge 400 Machine Data").Range("A3:Z" & i - 1).Copy
Sheets("Ridge 400 Machine Batch").Cells(3, 1).PasteSpecial Paste:=xlValues
''''TIME STAMP LOAD IN
Sheets("Ridge 400 Machine Batch").Range("AM3").Value = Now
'Print
Set ws = Sheets("Ridge400 Label")
Set WshNetwork = CreateObject("WScript.Network")
Sheets("Ridge400 Label").PrintOut ActivePrinter:="Ridge400 Label"
'''Remove data from the original DATA Sheet from Ridge 400 Machine Sheet where the Batch originated from
ThisWorkbook.Sheets("Ridge 400 Machine Data").Rows("3:" & i - 1).Delete
'''WIRTE Zeros if No QTY/Length is present to enforce machine register START
Set ws = Sheets("Ridge 400 Machine Batch")
For r = 4 To 14
If ws.Range("F" & r).Value = "" Then ws.Range("F" & r & ":G" & r) = "0"
Next r
'''Tell the machine the job has been loaded
Sheets("Ridge 400 Machine Batch").Range("R3") = "1"
'Application.Wait (Now + TimeValue("0:00:05"))
End If
End If
'''New code - test first
If Sheets("Ridge 400 Machine Batch").Range("AK3") = 1 Then
Sheets("Ridge 400 Machine Batch").Range("R3") = 0
Sheets("Ridge 400 Machine Batch").Range("AJ3") = 0
End If
Call CheckCoilDownpipe
Application.ScreenUpdating = True
End Sub
Sub movecompletedRidge400()
Application.ScreenUpdating = False
Dim ws As Worksheet, Lastrow As Long
Dim ws2 As Worksheet
Set ws = Sheets("Ridge 400 Machine Batch")
''Move the job to daily recorded sheet
'ThisWorkbook.Sheets("Ridge 400Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Ridge 400Machine").Range("B6").Value ''AS B2 in Data sheet is always going to be current First in First out concept
For i = 4 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Ridge 400 Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Ridge 400 Machine Batch").Range("A3").Value Then
Exit For
End If
Next i
ws.Range("A3:AN" & i - 1).Copy
Set ws2 = Sheets("Ridge400 Job List")
Lastrow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row + 1
Sheets("Ridge400 Job List").Range("A" & Lastrow).PasteSpecial Paste:=xlPasteValues
'Sheets("Gutter").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).PasteSpecial Paste:=xlPasteValues
Call ClearRidge400
Application.ScreenUpdating = True
End Sub
Sub ClearRidge400()
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = Sheets("Ridge 400 Machine Batch")
With ws
For i = 4 To 50000 ''count how many lines for this job (as jobs will always be stacked together this is easy to find
If Not ThisWorkbook.Sheets("Ridge 400 Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Ridge 400 Machine Batch").Range("A3").Value Then
Exit For
End If
Next i
.Range("A3:CC" & i - 1).ClearContents
.Range("AJ3") = 4
End With
Call LoadRidge400
Application.ScreenUpdating = True
End Sub
Sub CheckCoilDownpipe()
Application.ScreenUpdating = False
Dim ws As Worksheet, Lastrow As Long
Dim ws2 As Worksheet
Set ws = Sheets("Downpipe Machine Batch")
If Sheets("Downpipe Machine Batch").Range("AP3") = 0 And Sheets("Downpipe Machine Batch").Range("AQ3") = 1 Then
Sheets("Downpipe Machine Batch").Range("AQ3") = 0
End If
If Sheets("Downpipe Machine Batch").Range("AP3") = 1 And Sheets("Downpipe Machine Batch").Range("AQ3") = 0 Then
ws.Range("AR3:AZ26").Copy
Set ws2 = Sheets("Downpipe Job List")
Lastrow = ws2.Cells(ws2.Rows.Count, "AS").End(xlUp).Row + 1
Sheets("Downpipe Job List").Range("AR" & Lastrow).PasteSpecial Paste:=xlPasteValues
Sheets("Downpipe Machine Batch").Range("AQ3") = 1
End If
Call CheckCoilRidge300
Application.ScreenUpdating = True
End Sub
Sub CheckCoilRidge300()
Application.ScreenUpdating = False
Dim ws As Worksheet, Lastrow As Long
Dim ws2 As Worksheet
Set ws = Sheets("Ridge 300 Machine Batch")
If Sheets("Ridge 300 Machine Batch").Range("AP3") = 0 And Sheets("Ridge 300 Machine Batch").Range("AQ3") = 1 Then
Sheets("Ridge 300 Machine Batch").Range("AQ3") = 0
End If
If Sheets("Ridge 300 Machine Batch").Range("AP3") = 1 And Sheets("Ridge 300 Machine Batch").Range("AQ3") = 0 Then
ws.Range("AR3:AZ26").Copy
Set ws2 = Sheets("Ridge300 Job List")
Lastrow = ws2.Cells(ws2.Rows.Count, "AS").End(xlUp).Row + 1
Sheets("Ridge300 Job List").Range("AR" & Lastrow).PasteSpecial Paste:=xlPasteValues
Sheets("Ridge 300 Machine Batch").Range("AQ3") = 1
End If
Call CheckCoilRidge400
Application.ScreenUpdating = True
End Sub
Sub CheckCoilRidge400()
Application.ScreenUpdating = False
Dim ws As Worksheet, Lastrow As Long
Dim ws2 As Worksheet
Set ws = Sheets("Ridge 400 Machine Batch")
If Sheets("Ridge 400 Machine Batch").Range("AP3") = 0 And Sheets("Ridge 400 Machine Batch").Range("AQ3") = 1 Then
Sheets("Ridge 400 Machine Batch").Range("AQ3") = 0
End If
If Sheets("Ridge 400 Machine Batch").Range("AP3") = 1 And Sheets("Ridge 400 Machine Batch").Range("AQ3") = 0 Then
ws.Range("AR3:AZ26").Copy
Set ws2 = Sheets("Ridge400 Job List")
Lastrow = ws2.Cells(ws2.Rows.Count, "AS").End(xlUp).Row + 1
Sheets("Ridge400 Job List").Range("AR" & Lastrow).PasteSpecial Paste:=xlPasteValues
Sheets("Ridge 400 Machine Batch").Range("AQ3") = 1
End If
Call CheckCoilCorner
Application.ScreenUpdating = True
End Sub
Sub CheckCoilCorner()
Application.ScreenUpdating = False
Dim ws As Worksheet, Lastrow As Long
Dim ws2 As Worksheet
Set ws = Sheets("Corner Flashing Machine Batch")
If Sheets("Corner Flashing Machine Batch").Range("AP3") = 0 And Sheets("Corner Flashing Machine Batch").Range("AQ3") = 1 Then
Sheets("Corner Flashing Machine Batch").Range("AQ3") = 0
End If
If Sheets("Corner Flashing Machine Batch").Range("AP3") = 1 And Sheets("Corner Flashing Machine Batch").Range("AQ3") = 0 Then
ws.Range("AR3:AZ26").Copy
Set ws2 = Sheets("Corner Flashing Job List")
Lastrow = ws2.Cells(ws2.Rows.Count, "AS").End(xlUp).Row + 1
Sheets("Corner Flashing Job List").Range("AR" & Lastrow).PasteSpecial Paste:=xlPasteValues
Sheets("Corner Flashing Machine Batch").Range("AQ3") = 1
End If
Call CheckCoilBarge
Application.ScreenUpdating = True
End Sub
Sub CheckCoilBarge()
Application.ScreenUpdating = False
Dim ws As Worksheet, Lastrow As Long
Dim ws2 As Worksheet
Set ws = Sheets("Barge Machine Batch")
If Sheets("Barge Machine Batch").Range("AP3") = 0 And Sheets("Barge Machine Batch").Range("AQ3") = 1 Then
Sheets("Barge Machine Batch").Range("AQ3") = 0
End If
If Sheets("Barge Machine Batch").Range("AP3") = 1 And Sheets("Barge Machine Batch").Range("AQ3") = 0 Then
ws.Range("AR3:AZ26").Copy
Set ws2 = Sheets("Barge Job List")
Lastrow = ws2.Cells(ws2.Rows.Count, "AS").End(xlUp).Row + 1
Sheets("Barge Job List").Range("AR" & Lastrow).PasteSpecial Paste:=xlPasteValues
Sheets("Barge Machine Batch").Range("AQ3") = 1
End If
Call CheckCoilGutter
Application.ScreenUpdating = True
End Sub
Sub CheckCoilGutter()
Application.ScreenUpdating = False
Dim ws As Worksheet, Lastrow As Long
Dim ws2 As Worksheet
Set ws = Sheets("Gutter Machine Batch")
If Sheets("Gutter Machine Batch").Range("AP3") = 0 And Sheets("Gutter Machine Batch").Range("AQ3") = 1 Then
Sheets("Gutter Machine Batch").Range("AQ3") = 0
End If
If Sheets("Gutter Machine Batch").Range("AP3") = 1 And Sheets("Gutter Machine Batch").Range("AQ3") = 0 Then
ws.Range("AR3:AZ26").Copy
Set ws2 = Sheets("Gutter Job List")
Lastrow = ws2.Cells(ws2.Rows.Count, "AS").End(xlUp).Row + 1
Sheets("Gutter Job List").Range("AR" & Lastrow).PasteSpecial Paste:=xlPasteValues
Sheets("Gutter Machine Batch").Range("AQ3") = 1
End If
Application.ScreenUpdating = True
Call Timercontrol
End SubAny outside eyes welcomed.. perhaps I'm too close to this. (Happy to give the original .XLSM to whomever to help troubleshoot at a moments notice also.
Luke
