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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Try adding Application.Screenupdating = False at the beginning of each sub?
 
Last edited:
Upvote 0
wwbwb said:
Try adding Application.Screenupdating = False at the beginning of each sub?
Click to expand...


Hello there,

Thanks for your suggestion tried already sorry should of mentioned!

No good unfortunately.

With("sheets1")
end with

Works wonderfully however, it doesn't go into the physical sheet only when you're within does it act upon values found.
 
Upvote 0
I see lots of your code that should be modified.

Here in red is how you wrote the code and then see how I wrote the code.

Code: 
Sub My_Sub()
'Modified  10/29/2018  9:38:55 PM  EDT
If ActiveSheet.Range("P3").Value = "1" And ActiveSheet.Range("R3").Value = "4" Then
                
                [COLOR=#ff0000]'Sheets("C15015 Machine Data").Select
                'If Range("A3") >= 1 Then
                'Sheets("C15015 Machine Batch").Activate
                'ActiveSheet.Range("R3") = "1"[/COLOR]
                
        If Sheets("C15015 Machine Data").Range("A3") >= 1 Then
                Sheets("C15015 Machine Batch").Range("R3") = "1"
        End If
End If
End Sub
 
Upvote 0
I think wwbwb was suggesting screenupdating needs to be applied to each sub ?
Also, some changes could be applied ....for example....
This bit of code....
Code: 
 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


Can be reduced to........

Code: 
Sub MM1()
Dim r As Long, ws As Worksheet
Application.ScreenUpdating = False
Set ws = Sheets("C15015 Machine Batch")
For r = 4 To 14
    If ws.Range("F" & r).Value = "" Then ws.Range("F" & r & ":G" & r) = "0"
Next r
Application.ScreenUpdating = True
End Sub
 
Upvote 0
My Aswer Is This said:
I see lots of your code that should be modified.

Here in red is how you wrote the code and then see how I wrote the code.

Code: 
Sub My_Sub()
'Modified  10/29/2018  9:38:55 PM  EDT
If ActiveSheet.Range("P3").Value = "1" And ActiveSheet.Range("R3").Value = "4" Then
                
                [COLOR=#ff0000]'Sheets("C15015 Machine Data").Select
                'If Range("A3") >= 1 Then
                'Sheets("C15015 Machine Batch").Activate
                'ActiveSheet.Range("R3") = "1"[/COLOR]
                
        If Sheets("C15015 Machine Data").Range("A3") >= 1 Then
                Sheets("C15015 Machine Batch").Range("R3") = "1"
        End If
End If
End Sub
Click to expand...

I see, that is extremely helpful thank you. However my question would be when entering that sub routine how does it select that sheet?
 
Upvote 0
Michael M said:
I think wwbwb was suggesting screenupdating needs to be applied to each sub ?
Also, some changes could be applied ....for example....
This bit of code....
Code: 
 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


Can be reduced to........

Code: 
Sub MM1()
Dim r As Long, ws As Worksheet
Application.ScreenUpdating = False
Set ws = Sheets("C15015 Machine Batch")
For r = 4 To 14
    If ws.Range("F" & r).Value = "" Then ws.Range("F" & r & ":G" & r) = "0"
Next r
Application.ScreenUpdating = True
End Sub
Click to expand...

My very next question thank you.
However, does your code look at those cells to determine if they are truly blank or does it just say IF f4 is "" then 0 the entire range.
 
Upvote 0
It's exactly the same code as yours......just written differently !!
It does exactly the same thing !!
 
Upvote 0
Michael M said:
It's exactly the same code as yours......just written differently !!
It does exactly the same thing !!
Click to expand...

Thanks Michael, as you can tell from my code it's very novice these fancy things you do go way over my head...really need to sit down one of these days and learn properly.
 
Upvote 0
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Not running code
Replies
0
Views
207
MagicCarpet
M
M
  • Question Question
Using VBA code in a protected excel sheet
Replies
2
Views
516
MagicCarpet
M
R
  • Question Question
VBA- Adding source and destination cells together rather than overwriting
Replies
4
Views
250
Alex Blakenburg
A
T
  • Question Question
Printing to PDF use VBA - Was working but now different sheets are different widths
Replies
1
Views
205
Tanquen
T
K
  • Question Question
Select the Name in the Data Validation list on a cell value then the same name run the sub macro
Replies
9
Views
392
kpmsivaprakasam2003
K

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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