Stop the flicker

L

Lukums

Board Regular
Joined
Nov 23, 2015
Messages
195
Hello,

Briefly I have a loop that runs in our automation factory 24 hours / 5 days a week.

Runs beautifully.

HOWEVER, the screen flickering is doing my head in.

Ultimately this is because of my bad habit of .Activate
I tried using WITH but the issue is it needs to filter through each sheet looking for values and then doing XYZ.

So all I need is a solution to selecting each sheet and selecting the next without using .activate because I'm pretty sure this is the root cause.

Cheers!

Code: 
Public v As Integer
Sub BeginAutomation()


Dim v As Long


With Application
.EnableEvents = False
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With


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:
    
   
End Sub
Sub STOPAUTOMATION()


Range("XFC8").Value = "1"
ActiveWorkbook.Save
If Range("XFC8").Value = "1" Then Call EXITAUTO


End Sub


Sub TimerControl()


Dim v As Long


If v = 0 Then


    TimeToRun = Now + TimeValue("00:00:07")
    Application.OnTime TimeToRun, "LoadC15015"
    Else
    Exit Sub
    End If


End Sub


Sub LoadC15015()


 Dim WshNetwork
 Set WshNetwork = CreateObject("wscript.network")


 With Application
                        .EnableEvents = False
                        .Calculation = xlCalculationManual
                        .ScreenUpdating = False
                End With
                
                ''' LOAD JOB op PUSH
                
                ThisWorkbook.Sheets("C15015 Machine Batch").Activate
                
                'O3 = Job/Batch Complete
                'P3 = Opperator is ready for the next job
                'R3 = Unique Flag
                
                If ActiveSheet.Range("P3").Value = "1" And ActiveSheet.Range("R3").Value = "0" Then
             
                ActiveSheet.Range("R3") = "2"
                
                ActiveSheet.Range("AQ3") = "1"
                
                Range("AO3").Value = Now
                
                Call PrintToSelectedPrinterC15015
                
                End If
                
                '''''''''''''''''
                ''Run the Load''
                ''Upon STARTUP you need to write the 0 to each machine to ensure the fresh load.
                ''One shot trigger the job'' to ensure no double load
                '''''''''''''''''
                                
                If ActiveSheet.Range("P3").Value = "1" And ActiveSheet.Range("R3").Value = "4" Then
                Sheets("C15015 Machine Data").Select
                If Range("A3") >= 1 Then
                Sheets("C15015 Machine Batch").Activate
                ActiveSheet.Range("R3") = "1"
                
                
                
                      '''Print Label FLAG
                            'ActiveSheet.Range("AQ3").Value = "1"
                            
                            'If Range("AQ3").Value = "1" Then
                            'Sheets("C15015 Label").Select
                            ''WshNetwork.SetDefaultPrinter "C15015 Printer"
                            'ActiveSheet.PrintOut ActivePrinter:="C15015 Printer"
                            'Sheets("C15015 Machine Batch").Select
                            'Range("AQ3").Value = "0"
                            'End If
                            
                
                '''''''''''''''''
                
                ''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").Select  ''copy data to main menu page
                            ThisWorkbook.Sheets("C15015 Machine Data").Range("A3:M" & i - 1).Select
                            Selection.Copy
                            Sheets("C15015 Machine Batch").Select
                            ThisWorkbook.Sheets("C15015 Machine Batch").Cells(3, 1).Select
                            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                            False, Transpose:=False
                            
                            
                            'Sheets("C15015 Machine Data").Select  ''copy data to main menu page
                            'ThisWorkbook.Sheets("C15015 Machine Data").Range("I3:M" & i - 1).Select
                            'Selection.Copy
                            'Sheets("C15015 Machine Batch").Select
                            'ThisWorkbook.Sheets("C15015 Machine Batch").Cells(3, 9).Select
                            'Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                            'False, Transpose:=False
                            
                            
                            Range("AN3").Value = Now
                            
                            '''JOB HAS LOADED SEND TO GRANT
                             ActiveSheet.Range("Q3") = "1"
                                                       
                            
                '''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 ''delete the data that you are currently using from main table
                
                '''WIRTE Zeros if No QTY/Length is present to enforce machine register START
                
                If ThisWorkbook.Sheets("C15015 Machine Batch").Range("F4").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("F4:G4") = "0"
                End If
                If ThisWorkbook.Sheets("C15015 Machine Batch").Range("F5").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("F5:G5") = "0"
                End If
                If ThisWorkbook.Sheets("C15015 Machine Batch").Range("F6").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("F6:G6") = "0"
                End If
                If ThisWorkbook.Sheets("C15015 Machine Batch").Range("F7").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("F7:G7") = "0"
                End If
                If ThisWorkbook.Sheets("C15015 Machine Batch").Range("F8").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("F8:G8") = "0"
                End If
                If ThisWorkbook.Sheets("C15015 Machine Batch").Range("F9").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("F9:G9") = "0"
                End If
                If ThisWorkbook.Sheets("C15015 Machine Batch").Range("F10").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("F10:G10") = "0"
                End If
                If ThisWorkbook.Sheets("C15015 Machine Batch").Range("F11").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("F11:G11") = "0"
                End If
                If ThisWorkbook.Sheets("C15015 Machine Batch").Range("F12").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("F12:G12") = "0"
                End If
                If ThisWorkbook.Sheets("C15015 Machine Batch").Range("F13").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("F13:G13") = "0"
                End If
                If ThisWorkbook.Sheets("C15015 Machine Batch").Range("F14").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("F14:G14") = "0"
                End If
                 End If
                    End If
            
            
            '''PUNCHING o's
            '''row one
            
            
                If ThisWorkbook.Sheets("C15015 Machine Batch").Range("I3").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("I3:I3") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("J3").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("J3:J3") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("K3").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("K3:K3") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("L3").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("L3:L3") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("M3").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("M3:M3") = "0"
                End If
                
                
                '''PUNCHING o's
                '''row two
                
                
                If ThisWorkbook.Sheets("C15015 Machine Batch").Range("I4").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("I4:I4") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("J4").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("J4:J4") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("K4").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("K4:K4") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("L4").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("L4:L4") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("M4").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("M4:M4") = "0"
                End If
                
                '''PUNCHING o's
                '''row three
            
            
                If ThisWorkbook.Sheets("C15015 Machine Batch").Range("I5").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("I5:I5") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("J5").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("J5:J5") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("K5").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("K5:K5") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("L5").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("L5:L5") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("M5").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("M5:M5") = "0"
                End If
                
                '''PUNCHING o's
                '''row four
                
                
                If ThisWorkbook.Sheets("C15015 Machine Batch").Range("I6").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("I6:I6") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("J6").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("J6:J6") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("K6").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("K6:K6") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("L6").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("L6:L6") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("M6").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("M6:M6") = "0"
                End If
                
                 '''PUNCHING o's
                '''row five
            
            
                If ThisWorkbook.Sheets("C15015 Machine Batch").Range("I7").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("I7:I7") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("J7").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("J7:J7") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("K7").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("K7:K7") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("L7").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("L7:L7") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("M7").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("M7:M7") = "0"
                End If
                
                '''PUNCHING o's
                '''row six
                
                
                If ThisWorkbook.Sheets("C15015 Machine Batch").Range("I8").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("I8:I8") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("J8").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("J8:J8") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("K8").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("K8:K8") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("L8").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("L8:L8") = "0"
                End If
                 If ThisWorkbook.Sheets("C15015 Machine Batch").Range("M8").Value = "" Then
                ThisWorkbook.Sheets("C15015 Machine Batch").Range("M8:M8") = "0"
                End If
            
             '''Grant's magic
                            If ActiveSheet.Range("S3") = "1" Then
                            ActiveSheet.Range("R3") = "0"
                            ActiveSheet.Range("Q3") = "0"
                            End If
                '''END the Enforcement Section for Qty/Length
                 
                With Application
                        .EnableEvents = True
                        .Calculation = xlCalculationAutomatic
                        .ScreenUpdating = True
                End With
                
        ''' Goes through the Downpipe Info finds nothing then calls whatever is next int his case with only 1 machine it re-calls the start of the sub BeginAutomation
        ''' Once adding more machines to this module, we will need to change the below to call the next sub routine or next machine to find values.
        
        Call LoadC15024
                'End If
                
                '''IF nothing call next machine
                
End Sub


Sub movecompletedc15015()


 With Application
                        .EnableEvents = False
                        .Calculation = xlCalculationManual
                        .ScreenUpdating = False
                End With
                
                ''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
                
                            Sheets("C15015 Machine Batch").Select  ''copy data to main menu page
                            ThisWorkbook.Sheets("C15015 Machine Batch").Range("A3:Z" & i - 1).Select
                            Selection.Copy
                            
                            ''' Open Completed Job Location
                            '''Luke you changed the path from your desktop location to the network path under the ROOT C Section Location for localisation (DONE 21/06/2018) - Confirm work
                                Workbooks.Open Filename:="C:\Users\Luke\Desktop\RecordedDailyJobs.xlsm"
                            '''
                                                                               
                            Call RecordC15015
                            
                            '''
                
                With Application
                        .EnableEvents = True
                        .Calculation = xlCalculationAutomatic
                        .ScreenUpdating = True
                End With


End Sub


Sub RecordC15015()


Sheets("Sheet1").Select


lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
        


    Sheets("Sheet1").Activate


        erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close


Call ClearC15015


End Sub


Sub ClearC15015()


Sheets("C15015 Machine Batch").Select


            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
                
                            Sheets("C15015 Machine Batch").Select  ''copy data to main menu page
                            
                            ThisWorkbook.Sheets("C15015 Machine Batch").Range("A3:CC" & i - 1).Select
                            
                            Selection.ClearContents
                           
                        
                            'Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
                            'False, Transpose:=False
                
                        ActiveSheet.Range("R3") = "4"
                        ActiveSheet.Range("AQ3") = "0"
                        
                        '''Loaded Job Formula
                        'ThisWorkbook.Sheets("Formuals").Range("O3").Copy Sheets("Ridge 400 Machine").Range("O3")
                           
                  '' Call next machine ''
                  '''Always call BeginAutomation after a clear/load
                  
            Call LoadC15015


End Sub
Sub LoadC15024()


               With Application
                        .EnableEvents = False
                        .Calculation = xlCalculationManual
                        .ScreenUpdating = False
                End With
                
                ''' LOAD JOB op PUSH
                
                ThisWorkbook.Sheets("C15024 Machine Batch").Activate
                
                'N3 = 3 Batch Completed
                'P3 = 1 Opperator is ready for next job
                'AJ3 = 2 Unique flag which I'm confident we won't need as we do a one shot
                
                
                '''COMPLETED JOB SECTION
                'If ActiveSheet.Range("O3").Value = "3" And ActiveSheet.Range("P3").Value = "1" And ActiveSheet.Range("AJ3").Value = "0" Then
                If ActiveSheet.Range("P3").Value = "1" And ActiveSheet.Range("R3").Value = "0" Then
                
                ''Won't run again until we get grant's magic
                ActiveSheet.Range("R3") = "2"
                
                ActiveSheet.Range("AQ3") = "1"
                
                Range("AO3").Value = Now
                
                Call PrintToSelectedPrinterC15024
                
                End If


                '''''''''''''''''
                ''Run the Load''
                ''Upon STARTUP you need to write the 0 to each machine to ensure the fresh load.
                ''One shot trigger the job'' to ensure no double load
                '''''''''''''''''
                                                
                If ActiveSheet.Range("P3").Value = "1" And ActiveSheet.Range("R3").Value = "4" Then
                Sheets("C15024 Machine Data").Select
                If Range("A3") >= 1 Then
                Sheets("C15024 Machine Batch").Activate
                ActiveSheet.Range("R3") = "3"
                
                '''''''''''''''''
                
                ''Load next job
                ThisWorkbook.Sheets("C15024 Machine Batch").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 Batch").Range("B6").Value Then
                         Exit For
                        End If
                    Next i
                            '''Select Raw Data List
                            Sheets("C15024 Machine Data").Select  ''copy data to main menu page
                            ThisWorkbook.Sheets("C15024 Machine Data").Range("A3:M" & i - 1).Select
                            Selection.Copy
                            '''Select Machine Data (PLC Page)
                            Sheets("C15024 Machine Batch").Select
                            ThisWorkbook.Sheets("C15024 Machine Batch").Cells(3, 1).Select
                            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                            False, Transpose:=False
                            
                            Range("AN3").Value = Now
                            
                            '''Grab Punching Data
                            'Sheets("C15024 Machine Data").Select  ''copy data to main menu page
                            'ThisWorkbook.Sheets("C15024 Machine Data").Range("I3:M" & i - 1).Select
                            'Selection.Copy
                            'Sheets("C15024 Machine Batch").Select
                            'ThisWorkbook.Sheets("C15024 Machine Batch").Cells(3, 9).Select
                            'Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                            'False, Transpose:=False
                            
                '''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
                
                '''WIRTE Zeros if No QTY/Length is present to enforce machine register START
                
                If ThisWorkbook.Sheets("C15024 Machine Batch").Range("F4").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("F4:G4") = "0"
                End If
                If ThisWorkbook.Sheets("C15024 Machine Batch").Range("F5").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("F5:G5") = "0"
                End If
                If ThisWorkbook.Sheets("C15024 Machine Batch").Range("F6").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("F6:G6") = "0"
                End If
                If ThisWorkbook.Sheets("C15024 Machine Batch").Range("F7").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("F7:G7") = "0"
                End If
                If ThisWorkbook.Sheets("C15024 Machine Batch").Range("F8").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("F8:G8") = "0"
                End If
                If ThisWorkbook.Sheets("C15024 Machine Batch").Range("F9").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("F9:G9") = "0"
                End If
                If ThisWorkbook.Sheets("C15024 Machine Batch").Range("F10").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("F10:G10") = "0"
                End If
                If ThisWorkbook.Sheets("C15024 Machine Batch").Range("F11").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("F11:G11") = "0"
                End If
                If ThisWorkbook.Sheets("C15024 Machine Batch").Range("F12").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("F12:G12") = "0"
                End If
                If ThisWorkbook.Sheets("C15024 Machine Batch").Range("F13").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("F13:G13") = "0"
                End If
                If ThisWorkbook.Sheets("C15024 Machine Batch").Range("F14").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("F14:G14") = "0"
                End If
                
                
                '''PUNCHING o's
            '''row one
            
            
                If ThisWorkbook.Sheets("C15024 Machine Batch").Range("I3").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("I3:I3") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("J3").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("J3:J3") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("K3").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("K3:K3") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("L3").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("L3:L3") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("M3").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("M3:M3") = "0"
                End If
                
                
                '''PUNCHING o's
                '''row two
                
                
                If ThisWorkbook.Sheets("C15024 Machine Batch").Range("I4").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("I4:I4") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("J4").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("J4:J4") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("K4").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("K4:K4") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("L4").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("L4:L4") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("M4").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("M4:M4") = "0"
                End If
                
                '''PUNCHING o's
                '''row three
            
            
                If ThisWorkbook.Sheets("C15024 Machine Batch").Range("I5").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("I5:I5") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("J5").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("J5:J5") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("K5").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("K5:K5") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("L5").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("L5:L5") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("M5").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("M5:M5") = "0"
                End If
                
                '''PUNCHING o's
                '''row four
                
                
                If ThisWorkbook.Sheets("C15024 Machine Batch").Range("I6").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("I6:I6") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("J6").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("J6:J6") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("K6").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("K6:K6") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("L6").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("L6:L6") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("M6").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("M6:M6") = "0"
                End If
                
                 '''PUNCHING o's
                '''row five
            
            
                If ThisWorkbook.Sheets("C15024 Machine Batch").Range("I7").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("I7:I7") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("J7").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("J7:J7") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("K7").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("K7:K7") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("L7").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("L7:L7") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("M7").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("M7:M7") = "0"
                End If
                
                '''PUNCHING o's
                '''row six
                
                
                If ThisWorkbook.Sheets("C15024 Machine Batch").Range("I8").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("I8:I8") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("J8").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("J8:J8") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("K8").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("K8:K8") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("L8").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("L8:L8") = "0"
                End If
                 If ThisWorkbook.Sheets("C15024 Machine Batch").Range("M8").Value = "" Then
                ThisWorkbook.Sheets("C15024 Machine Batch").Range("M8:M8") = "0"
                End If
                
                'Job Loaded - Tell Grant - Trigger Grant's Magic
                ActiveSheet.Range("Q3") = "1"
                
                '''AJ3 needs to be 0 awaiting batch complete and opperator load
                
            End If
            End If
            
             '''Grant's magic
                            If ActiveSheet.Range("S3") = "1" Then
                            ActiveSheet.Range("R3") = "0"
                            ActiveSheet.Range("Q3") = "0"
                            ActiveSheet.Range("AQ3") = "0"
                            End If
              
                  ThisWorkbook.Sheets("HOME").Activate
                  
                  Call TimerControl
                 
                With Application
                        .EnableEvents = True
                        .Calculation = xlCalculationAutomatic
                        .ScreenUpdating = True
                End With
                                               
End Sub


Sub movecompletec15024()


 With Application
                        .EnableEvents = False
                        .Calculation = xlCalculationManual
                        .ScreenUpdating = False
                End With
                
                ''Move the job to daily recorded sheet
                 'ThisWorkbook.Sheets("C15024 Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("C15024 Machine Data").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
                
                            Sheets("C15024 Machine Batch").Select  ''copy data to main menu page
                            ThisWorkbook.Sheets("C15024 Machine Batch").Range("A3:Z" & i - 1).Select
                            Selection.Copy
                            
                            ''' Open Completed Job Location
                            '''Luke you changed the path from your desktop location to the network path under the ROOT C Section Location for localisation (DONE 21/06/2018) - Confirm work
                                Workbooks.Open Filename:="C:\Users\Luke\Desktop\RecordedDailyJobs.xlsm"
                            '''
                                                                               
                            Call RecordC15024
                            
                            '''
                
                With Application
                        .EnableEvents = True
                        .Calculation = xlCalculationAutomatic
                        .ScreenUpdating = True
                End With




End Sub


Sub RecordC15024()


Sheets("Sheet1").Select


lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
        


    Sheets("Sheet1").Activate


        erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close


Call ClearC15024Batch


End Sub


Sub ClearC15024Batch()


Sheets("C15024 Machine Batch").Select


            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
                
                            Sheets("C15024 Machine Batch").Select  ''copy data to main menu page
                            
                            ThisWorkbook.Sheets("C15024 Machine Batch").Range("A3:CC" & i - 1).Select
                            
                            Selection.ClearContents
                           
                        
                            'Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
                            'False, Transpose:=False
                
                        ActiveSheet.Range("R3") = "4"
                        ActiveSheet.Range("AQ3") = "0"
                        
                        '''Loaded Job Formula
                           
                  '' Call next machine ''
                  '''Always call BeginAutomation after a clear/load
            Call LoadC15024
End Sub
 
Sort by date Sort by votes
To answer your question in post 6

You do not need to activate a sheet or select a range to do a lot of things.

Here are examples of two ways to do things.
A lot of people think you have to do it like this script named Their way.

But you can do it my way and get the same results with less code and will cause less flickering and may be faster.



Code: 
Sub Their_Way()
'Modified  10/29/2018  10:24:28 PM  EDT
Sheets("Dad").Activate
Range("A1").Copy
Sheets("Mom").Activate
Range("A1").Select
ActiveSheet.PasteSpecial
End Sub
Sub My_Way()
'Modified  10/29/2018  10:24:28 PM  EDT
Sheets("Dad").Range("A1").Copy Sheets("Mom").Range("A1")
End Sub
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Michael M said:
I'm hearin ya !!
If you work through the smaller codes and eliminate .Select / Selection and .Activate wherever possible, it will sped things up AND reduce the flicker.
Have a crack and if you strike problems with changes post the code back here for help correcting / modifying
Click to expand...

Michael! I'm getting the hang of it as I understand more however! I'm stuck on 2 things in particular - wb.save (it starts the sub routine) I just want it to continue doing what it needs to do...

Second thing I have a .select happening for next i but I can't work out how to obliterate this .select! You're help as always is appreciated.

Code: 
Sub LoadDownpipe()Application.ScreenUpdating = False
Dim r As Long, ws As Worksheet
Dim wb As Workbook
Set wb = Workbooks("Best Shed Scheduler Flashing 14112018 Development.xlsm")


'Can't use as it restarts SUB - how to avoid this?
'wb.save


              ThisWorkbook.Sheets("Downpipe Machine Batch").Activate
              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
              ActiveSheet.Range("AJ3") = "2"
                
                '''Job has been completed TIME STAMP
                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
                                
                'If ActiveSheet.Range("N3").Value = "3" And ActiveSheet.Range("P3").Value = "1" And ActiveSheet.Range("AJ3") = "4" Then
                'Sheets("Downpipe Machine Data").Select
                
                'NEW SHORT CODE
                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").Select  ''copy data to main menu page
                            ThisWorkbook.Sheets("Downpipe Machine Data").Range("A3:Z" & i - 1).Select
                            Selection.Copy
                            Sheets("Downpipe Machine Batch").Select
                            ThisWorkbook.Sheets("Downpipe Machine Batch").Cells(3, 1).Select
                            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                            False, Transpose:=False
                                                                                                                                                                                       
                ''''TIME STAMP LOAD IN
                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
                
                'Workbook SAVE on LOAD JOB
                'wb.Save
    
    '''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
                                               
                Sheets("Downpipe Machine Batch").Select
                
                '''Tell the machine the job has been loaded
                'ActiveSheet.Range("R3") = "1"
                 
                 Sheets("Downpipe Machine Batch").Range("R3") = "1"
                 Application.Wait (Now + TimeValue("0:00:05"))
            End If
            End If
            
             '''Grant's magic - NOTHING WORKS IF! Grant's Magic doesn't come on!
                            If ActiveSheet.Range("AK3") = "1" Then
                            ActiveSheet.Range("R3") = "0"
                            ActiveSheet.Range("AJ3") = "0"
                            End If
                                                                   
                    Call LoadGutter
                    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Change this

Code: 
Sheets("Downpipe Machine Data").Select  ''copy data to main menu page
                            ThisWorkbook.Sheets("Downpipe Machine Data").Range("A3:Z" & i - 1).Select
                            Selection.Copy
                            Sheets("Downpipe Machine Batch").Select
                            ThisWorkbook.Sheets("Downpipe Machine Batch").Cells(3, 1).Select
                            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                            False, Transpose:=False

To this

Code: 
  Sheets("Downpipe Machine Data").Range("A3:Z10").Copy
    Sheets("Downpipe Machine Batch").Cells(3, 1).PasteSpecial Paste:=xlValues
 
Upvote 0
Michael M said:
Change this

Code: 
Sheets("Downpipe Machine Data").Select  ''copy data to main menu page
                            ThisWorkbook.Sheets("Downpipe Machine Data").Range("A3:Z" & i - 1).Select
                            Selection.Copy
                            Sheets("Downpipe Machine Batch").Select
                            ThisWorkbook.Sheets("Downpipe Machine Batch").Cells(3, 1).Select
                            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                            False, Transpose:=False

To this

Code: 
  Sheets("Downpipe Machine Data").Range("A3:Z10").Copy
    Sheets("Downpipe Machine Batch").Cells(3, 1).PasteSpecial Paste:=xlValues
Click to expand...

Hello Michael much appreciated with your input however you're saying A3:Z10 is getting hard copied right?
The code just before this is counting how many rows to grab right? So isn't this a concern with your code? Sorry for questioning you, just curious as I'm currently unable to test right now.
 
Upvote 0
Sorry, my error....I changed the cell refs for my testing, it should read

Code: 
Sheets("Downpipe Machine Data").Range("A3:Z" & i - 1).Copy
Sheets("Downpipe Machine Batch").Cells(3, 1).PasteSpecial Paste:=xlValues
 
Upvote 0
Michael M said:
Sorry, my error....I changed the cell refs for my testing, it should read

Code: 
Sheets("Downpipe Machine Data").Range("A3:Z" & i - 1).Copy
Sheets("Downpipe Machine Batch").Cells(3, 1).PasteSpecial Paste:=xlValues
Click to expand...

Oh no, no need to be sorry! I thought this was as such all good made that change working well.

I'll continue by myself making these changes to get rid of these selects/activates.

As always two thumbs up for your help!
 
Upvote 0
Hello all,

Home stretch and let me tell you a million times better with all those selects/activates gone! - That said I still have a few left. I'll leave the guts of it alone for now. However I have 2 annoying things.

1. using wb.save will refresh the sub instead of continuing what I've asked it to do better way to save an active workbook instead of activeworkbook.save? I was using dim wb as workbook, set wb as activeworkbook, wb.save but as mentioned no good... as it does that refresh.

2. At the start of each LOAD(XYZ) eg: LOADDownpipe I have 1 initial sheets("Downpipe Machine Batch").activate - reason being is if the workbook isn't on that sheet at the time it won't hop into it. I've removed most of the silly selects/activates apart from the ones that start the SUB LOAD(XYZ) routines.

Code in FULL below:

Code: 
Public v As IntegerSub BeginAutomation()
'Dim wb As Workbook
'Set wb = Workbooks(“savefile.xlsm”)
'wb.Save
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
'v = 1
Exit Sub
Application.ScreenUpdating = True
End Sub
Sub Timercontrol()
Application.ScreenUpdating = False
    If v = 0 Then
    TimeToRun = Now + TimeValue("00:00:10")
    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 14112018 Development.xlsm")


'Can't use as it restarts SUB - how to avoid this?
'wb.save


            '''IF this line isn't here and the automation is checking other sheets through the many sub-routines it won't "Hop into" downpipe machine batch
              ThisWorkbook.Sheets("Downpipe Machine Batch").Activate
              
              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
              ActiveSheet.Range("AJ3") = "2"
                
                '''Job has been completed TIME STAMP
                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
                                
                'If ActiveSheet.Range("N3").Value = "3" And ActiveSheet.Range("P3").Value = "1" And ActiveSheet.Range("AJ3") = "4" Then
                'Sheets("Downpipe Machine Data").Select
                
                'NEW SHORT CODE
                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
                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
                
                'Workbook SAVE on LOAD JOB
                'wb.Save
    
    '''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
               
                ''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
                           
                           Sheets("Downpipe Machine Batch").Range("A3:AN" & i - 1).Copy
                          
Call RecordDownpipe
Application.ScreenUpdating = True
End Sub
Sub RecordDownpipe()
Dim lastrow As String
lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
        
Sheets("Downpipe").Activate
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Call ClearDownpipeBatch
End Sub
Sub ClearDownpipeBatch()
Application.ScreenUpdating = False
Sheets("Downpipe Machine Batch").Select


                        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
                
                            '''Clear the job that was previously manufactured
                            With Sheets("Downpipe Machine Batch")
                            ThisWorkbook.Sheets("Downpipe Machine Batch").Range("A3:CC" & i - 1).Select
                            Selection.ClearContents
                        
                       '''Unique Value indicating Job is in the ready position for NEW JOB - Waiting for Grant's Magic
                       Sheets("Downpipe Machine Batch").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 14112018 Development.xlsm")


'Can't use as it restarts SUB - how to avoid this?
'wb.save


              ThisWorkbook.Sheets("Gutter Machine Batch").Activate
              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
              ActiveSheet.Range("AJ3") = "2"
                
                '''Job has been completed TIME STAMP
                Range("AN3").Value = Now
                
                ''Engage the move of completed Gutter batch once completed triggers are found
                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
                                
                'If ActiveSheet.Range("N3").Value = "3" And ActiveSheet.Range("P3").Value = "1" And ActiveSheet.Range("AJ3") = "4" Then
                'Sheets("Gutter Machine Data").Select
                
                '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
                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
                
                'Workbook SAVE on LOAD JOB
                'wb.Save
    
    '''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
                                               
                Sheets("Gutter Machine Batch").Select
                
                '''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
            
             '''Grant's magic - NOTHING WORKS IF! Grant's Magic doesn't come on!
                            If ActiveSheet.Range("AK3") = "1" Then
                            ActiveSheet.Range("R3") = "0"
                            ActiveSheet.Range("AJ3") = "0"
                            End If


                                         
Call LoadBarge
Application.ScreenUpdating = True
End Sub
Sub movecompletedgutter()
Application.ScreenUpdating = False
             ''Move the job to daily recorded sheet
                 'ThisWorkbook.Sheets("Gutter Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Gutter Machine Data").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
                
                           Sheets("Gutter Machine Batch").Range("A3:AN" & i - 1).Copy
                            
                            ''' Open Completed Job Location - Save JOB into other Workbook
Call RecordGutter
Application.screeupdating = True
End Sub
Sub RecordGutter()
Application.ScreenUpdating = False
Dim lastrow As String


lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
        
Sheets("Gutter").Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False


Call ClearGutterBatch
Application.ScreenUpdating = True
End Sub
Sub ClearGutterBatch()
Application.ScreenUpdating = False


        Sheets("Gutter Machine Batch").Select


            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
                
                            Sheets("Gutter Machine Batch").Select  ''copy data to main menu page
                            ThisWorkbook.Sheets("Gutter Machine Batch").Range("A3:CC" & i - 1).Select
                            Selection.ClearContents
                            ActiveSheet.Range("AJ3") = "4"
                        
Application.ScreenUpdating = False
Call LoadGutter
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 14112018 Development.xlsm")


'Can't use as it restarts SUB - how to avoid this?
'wb.save
            ThisWorkbook.Sheets("Barge Machine Batch").Activate
              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
              ActiveSheet.Range("AJ3") = "2"
                
                '''Job has been completed TIME STAMP
                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
                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
                                               
                'Sheets("Barge Machine Batch").Select
                
                '''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
            
             '''Grant's magic - NOTHING WORKS IF! Grant's Magic doesn't come on!
                            If ActiveSheet.Range("AK3") = "1" Then
                            ActiveSheet.Range("R3") = "0"
                            ActiveSheet.Range("AJ3") = "0"
                            End If




                                                                  
Call LoadCornerFlashing
Application.ScreenUpdating = True
End Sub
Sub movecompletedbarge()
Application.ScreenUpdating = False
              
              ''Move the job to daily recorded sheet
                 'ThisWorkbook.Sheets("Barge Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Barge Machine Data").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
                
                            Sheets("Barge Machine Batch").Range("A3:AN" & i - 1).Copy
                                                                                                             
Call RecordBarge
Application.ScreenUpdating = True
End Sub
Sub RecordBarge()
Application.ScreenUpdating = False
Dim lastrow As String


lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Sheets("Barge").Activate
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False


Call ClearBargeBatch
Application.ScreenUpdating = True
End Sub
Sub ClearBargeBatch()
Application.ScreenUpdating = False
        Sheets("Barge Machine Batch").Select


            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
                
                            Sheets("Barge Machine Batch").Select  ''copy data to main menu page
                            ThisWorkbook.Sheets("Barge Machine Batch").Range("A3:CC" & i - 1).Select
                            Selection.ClearContents
                            ActiveSheet.Range("AJ3") = "4"
                                                                                                                      
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 14112018 Development.xlsm")


'Can't use as it restarts SUB - how to avoid this?
'wb.save


              ThisWorkbook.Sheets("Corner Flashing Machine Batch").Activate
              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
              ActiveSheet.Range("AJ3") = "2"
                
                '''Job has been completed TIME STAMP
                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
                                
                'If ActiveSheet.Range("N3").Value = "3" And ActiveSheet.Range("P3").Value = "1" And ActiveSheet.Range("AJ3") = "4" Then
                'Sheets("Corner Flashing Machine Data").Select
                
                '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 Batch").Cells(3, 1).PasteSpecial Paste:=xlValues
                                                                                                                                                                                       
                ''''TIME STAMP LOAD IN
                Range("AM3").Value = Now
                                                                                                
                '''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
                
                'Workbook SAVE on LOAD JOB
                'wb.Save
    
    '''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
                                               
                Sheets("Corner Flashing Machine Batch").Select
                
                '''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
            
             '''Grant's magic - NOTHING WORKS IF! Grant's Magic doesn't come on!
                            If ActiveSheet.Range("AK3") = "1" Then
                            ActiveSheet.Range("R3") = "0"
                            ActiveSheet.Range("AJ3") = "0"
                            End If
Call LoadRidge300
Application.ScreenUpdating = True
End Sub


Sub movecompletedcornerflashing()
Application.ScreenUpdating = False
                ''Move the job to daily recorded sheet
                 'ThisWorkbook.Sheets("Corner Flashing Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Corner Flashing Machine Data").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
                
                            Sheets("Corner Flashing Machine Batch").Range("A3:AN" & i - 1).Copy
                            
Call RecordCorner
Application.ScreenUpdating = True
End Sub


Sub RecordCorner()
Application.ScreenUpdating = False
Dim lastrow As String


lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Corner Flashing").Activate
        erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
                    
Call ClearCornerBatch
Application.ScreenUpdating = True
End Sub


Sub ClearCornerBatch()
Application.ScreenUpdating = False


ThisWorkbook.Sheets("Corner Flashing Machine Batch").Select


            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
                
                            Sheets("Corner Flashing Machine Batch").Select  ''copy data to main menu page
                                    ThisWorkbook.Sheets("Corner Flashing Machine Batch").Range("A3:CC" & i - 1).Select
                                        Selection.ClearContents
                                            ActiveSheet.Range("AJ3") = "4"


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 14112018 Development.xlsm")


'Can't use as it restarts SUB - how to avoid this?
'wb.save


              ThisWorkbook.Sheets("Ridge 300 Machine Batch").Activate
              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
              ActiveSheet.Range("AJ3") = "2"
                
                '''Job has been completed TIME STAMP
                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
                                
                'If ActiveSheet.Range("N3").Value = "3" And ActiveSheet.Range("P3").Value = "1" And ActiveSheet.Range("AJ3") = "4" Then
                'Sheets("Ridge 300 Machine Data").Select
                
                '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
                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
                
                'Workbook SAVE on LOAD JOB
                'wb.Save
    
    '''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
                                               
                Sheets("Ridge 300 Machine Batch").Select
                
                '''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
            
             '''Grant's magic - NOTHING WORKS IF! Grant's Magic doesn't come on!
                            If ActiveSheet.Range("AK3") = "1" Then
                            ActiveSheet.Range("R3") = "0"
                            ActiveSheet.Range("AJ3") = "0"
                            End If
                            
Call LoadRidge400
Application.ScreenUpdating = True
End Sub
Sub movecompletedridge300()
Application.ScreenUpdating = False
             
                ''Move the job to daily recorded sheet
                 'ThisWorkbook.Sheets("Ridge 300 Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Ridge 300 Machine Data").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
                
                           Sheets("Ridge 300 Machine Batch").Range("A3:AN" & i - 1).Copy
                                       
                        Call RecordRidge300
                        Application.ScreenUpdating = True
End Sub


Sub RecordRidge300()
Application.ScreenUpdating = False
Dim lastrow As String


lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Ridge300").Activate
        erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False


Call ClearRidge300Batch
Application.ScreenUpdating = True
End Sub


Sub ClearRidge300Batch()
Application.ScreenUpdating = False
     
Sheets("Ridge 300 Machine Batch").Select


            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
                
                            Sheets("Ridge 300 Machine Batch").Select  ''copy data to main menu page
                                 ThisWorkbook.Sheets("Ridge 300 Machine Batch").Range("A3:CC" & i - 1).Select
                                     Selection.ClearContents
                                        ActiveSheet.Range("AJ3") = "4"
                                                                                                       
Call LoadRidge300
Application.ScreenUpdating = True
End Sub


Sub LoadRidge400()
Application.ScreenUpdating = False
Dim WshNetwork
Set WshNetwork = CreateObject("WScript.Network")
Dim r As Long, ws As Worksheet


                  ThisWorkbook.Sheets("Ridge 400 Machine Batch").Activate
                
                'N3 = 3 Batch Completed
                'P3 = 1 Opperator is ready for next job
                'AJ3 = 2 Unique flag which I'm confident we won't need as we do a one shot
                              
                '''COMPLETED JOB SECTION
                'If ActiveSheet.Range("N3").Value = "3" And ActiveSheet.Range("P3").Value = "1" And ActiveSheet.Range("AJ3").Value = "0" Then
                If ActiveSheet.Range("N3").Value = "3" And ActiveSheet.Range("P3").Value = "1" And ActiveSheet.Range("AJ3").Value = "0" Then
                
                ''Won't run again until we get grant's magic
                ActiveSheet.Range("AJ3") = "2"
                
                '''JOB HAS BEEN COMPLTED TIME STAMP
                ActiveSheet.Range("AN3").Value = Now
                ActiveWorkbook.Save
                Call movecompletedridge400
                
                End If
             
                ''Run the Load''
                                              
               If ActiveSheet.Range("N3").Value = "3" And ActiveSheet.Range("P3").Value = "1" And ActiveSheet.Range("AJ3").Value = "4" Then
                    Sheets("Ridge 400 Machine Data").Select
                        If Range("A3") >= 1 Then
                             Sheets("Ridge 400 Machine Batch").Activate
                               Range("AJ3") = "3"
                
                ''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
                
                    Sheets("Ridge 400 Machine Data").Range("A3:Z" & i - 1).Copy
                    Sheets("Ridge 400 Machine Batch").Cells(3, 1).PasteSpecial Paste:=xlValues
                             
                 '''TIME STAMP JOB LOADED
                ActiveSheet.Range("AM3").Value = Now
                            
                 '''Print Label FLAG
                ActiveSheet.Range("AP3").Value = "1"
                 
                If Range("AP3").Value = 1 Then
                Sheets("Ridge400 Label").Select
                WshNetwork.SetDefaultPrinter "Ridge400"
                ActiveSheet.PrintOut ActivePrinter:="Ridge400"
                Sheets("Ridge 400 Machine Batch").Select
                ActiveSheet.Range("AP3").Value = "0"
                            
                '''Remove data from the original DATA Sheet from Ridge 400 Machine Data Sheet where the Batch originated from
                ThisWorkbook.Sheets("Ridge 400 Machine Data").Rows("3:" & i - 1).Delete ''delete the data that you are currently using from main table
                
    '''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
                
                'Load Job has now been loaded successfully
                ActiveSheet.Range("R3") = "1"
                Application.Wait (Now + TimeValue("0:00:05"))
                '''AJ3 needs to be 0 awaiting batch complete and opperator load
                
            End If
            End If
            End If
            
             '''Grant's magic
                            If ActiveSheet.Range("AK3") = "1" Then
                           ActiveSheet.Range("R3") = "0"
                           ActiveSheet.Range("AJ3") = "0"
                            End If
                '''END the Enforcement Section for Qty/Length
        '''Select HOME screen at the last sub - Is select really the best way to do this...? 14/09/2018
           
        Sheets("HOME").Select
        Call Timercontrol
        Application.ScreenUpdating = True
End Sub


Sub movecompletedridge400()


             Application.ScreenUpdating = False
             
                ''Move the job to daily recorded sheet
                 'ThisWorkbook.Sheets("Ridge 400 Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Ridge 400 Machine Data").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
                
                           Sheets("Ridge 400 Machine Batch").Range("A3:AN" & i - 1).Copy
                           '
                            Call RecordRidge400
                            Application.ScreenUpdating = True
End Sub
Sub RecordRidge400()
Application.ScreenUpdating = False
Dim lastrow As String
lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
        
        Sheets("Ridge400").Activate
        erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False


Call ClearRidge400Batch
Application.ScreenUpdating = True
End Sub


Sub ClearRidge400Batch()
Application.ScreenUpdating = False
 
Sheets("Ridge 400 Machine Batch").Select


            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
                
                            Sheets("Ridge 400 Machine Batch").Select  ''copy data to main menu page
                            ThisWorkbook.Sheets("Ridge 400 Machine Batch").Range("A3:CC" & i - 1).Select
                            Selection.ClearContents
                            ActiveSheet.Range("AJ3") = "4"
                 
            Call LoadRidge400
            Application.ScreenUpdating = True
            
End Sub
 
Upvote 0
Still a bit to do
You shouldn't have to "hop into" a sheet to do stuff....see below

This
Code: 
              ThisWorkbook.Sheets("Downpipe Machine Batch").Activate
              
              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
              ActiveSheet.Range("AJ3") = "2"
                
                '''Job has been completed TIME STAMP
                Range("AN3").Value = Now

could be reduced to this without activating the sheet

Code: 
With Sheets("Downpipe Machine Batch")
    If .Range("N3") = 3 And .Range("P3") = 1 And .Range("AJ3") = 0 Then .Range("AJ3") = "2"
    '''Job has been completed TIME STAMP
        .Range("AN3").Value = Now
End With
 
Last edited:
Upvote 0
wb.Save shouldn't restart the sub unless wb isn't open.
It should work fine if wb is the activeworkbook !!


I note also that you are copying a block of data>> activating another sheet >>then pasting that data, when you could simply paste the data straight to the sheet, almost eliminating the need for a number of extra macros !

Also noticed there are blocks of comments that are redundant....I'd suggest removing as many as you can, for ease of debugging
 
Last edited:
Upvote 0
Michael M said:
Still a bit to do
You shouldn't have to "hop into" a sheet to do stuff....see below

This
Code: 
              ThisWorkbook.Sheets("Downpipe Machine Batch").Activate
              
              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
              ActiveSheet.Range("AJ3") = "2"
                
                '''Job has been completed TIME STAMP
                Range("AN3").Value = Now

could be reduced to this without activating the sheet

Code: 
With Sheets("Downpipe Machine Batch")
    If .Range("N3") = 3 And .Range("P3") = 1 And .Range("AJ3") = 0 Then .Range("AJ3") = "2"
    '''Job has been completed TIME STAMP
        .Range("AN3").Value = Now
End With
Click to expand...

I had...no idea... so much to learn Q.Q but it's good though! Thank you... putting these comments in as we go to better teach myself moving forward and hopefully (one day) relay this message to help others!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
VBA Macros running slow out of nowhere
Replies
3
Views
196
mumps
mumps
H
  • Question
Compare Demand Data to Stock Data
Replies
6
Views
347
Bosquedeguate
Bosquedeguate
D
  • Question
VBA user form and barcode scannig problem
Replies
1
Views
132
bobsan42
bobsan42
JeffGrant
  • Question
Running cmd batch file repeatedly...
Replies
1
Views
377
offthelip
O
S
  • Solved
Copying Filtered Columns (non-adjacent) from one Table to end of another, but need to also add information in adjacent column of Destination Table
Replies
1
Views
344
sspatriots
S

Forum statistics

Threads
1,214,641
Messages
6,120,691
Members
448,978
Latest member
rrauni

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top