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
Yep, agreed about the comments, but when you change code lines, leave in the old ones >> comment them out AND put in a comment that you have replaced them...that's redundant.
If you have moved on from old lines of code to better ones AND they work....get rid of 'em.

Keep up the good work you're getting there...:beerchug:
 
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
@Lukums
What does this code do ?
and where is it fired from ?

Code: 
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
 
Upvote 0
Michael M said:
@Lukums
What does this code do ?
and where is it fired from ?

Code: 
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
Click to expand...
@Michael M

The code comes from a button on the FRONT END UI which stops the automation loop.

The loop runs 24 hours 6 days a week on an ENDLESS never ending loop.
Once all the jobs are completed for the week the guys hit STOP allowing them to input the next lot from SQL.
 
Upvote 0
Michael M said:
Ok.....just playing with your codes...:beerchug:
Click to expand...

All good! You go for gold mate.

I did change a few things to start going with / end with with .RANGE instead of hard selecting the sheets.

But I have so many IF statements with no idea how to NEST the With / end withs that said I haven't given up yet!

Cheers for your help
 
Upvote 0
Michael M said:
No worries...yell out if you get stuck.
Click to expand...
@Michael M

I'm really disappointed in myself... I can't get this to work.. coping data from one sheet to another -
Can easily do it with select... and erow... but I'm trying to avoid this but I CAN'T for the life of me work it out...

Here is the snip of code:

Code: 
Sub movecompleteddownpipe()Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = Sheets("Downpipe Machine Batch")
                ''Move the job to daily recorded sheet
                 'ThisWorkbook.Sheets("Downpipe Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Downpipe Machine").Range("B6").Value  ''AS B2 in Data sheet is always going to be current First in First out concept
                    For i = 4 To 50000  ''count how many lines for this job (as jobs will always be stacked together this is easy to find
                        If Not ThisWorkbook.Sheets("Downpipe Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Downpipe Machine Batch").Range("A3").Value Then
                         Exit For
                        End If
                    Next i
                           
                           Sheets("Downpipe Machine Batch").Range("A3:AN" & i - 1).Copy
                          
Call RecordDownpipe
Application.ScreenUpdating = True
End Sub
Sub RecordDownpipe()


'With Sheets("Downpipe")
Dim ws As Worksheet
Set ws = Sheets("Downpipe")


Sheets("Downpipe").Cells(Rows.Count, "A").End(xlUp).Row. _
PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False




'Dim lastrow As String
'lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
'With ThisWorkbook.Worksheets("Downpipe")
'Sheets("Downpipe").Select
'erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'lastrow = MasterFile.Sheets(1).Range("A1").End(xlDown).Row + 1
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'ActiveSheet.Cells(erow, 1).Select
'ActiveSheet.Paste
'End With
Application.CutCopyMode = False


Call ClearDownpipeBatch
End Sub
Sub ClearDownpipeBatch()
Application.ScreenUpdating = False
ThisWorkbook.Sheets("Downpipe Machine Batch").Activate


                        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:CC" & i - 1).ClearContents
                                                
                            '''Clear the job that was previously manufactured
                            Sheets("Downpipe Machine Batch").Select
                            Sheets("Downpipe Machine Batch").Range("A3:CC" & i - 1).ClearContents
                            Selection.ClearContents
                            '''Sheets("Downpipe Machine Data").Range("A3:Z" & i - 1).Copy
                       '''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
 
Upvote 0
MAybe this, you didn't teel the cpoied dat where to be pasted, ie whereabouts in column "A"



Code: 
Sub movecompleteddownpipe()
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = Sheets("Downpipe Machine Batch")
                ''Move the job to daily recorded sheet
                 'ThisWorkbook.Sheets("Downpipe Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Downpipe Machine").Range("B6").Value  ''AS B2 in Data sheet is always going to be current First in First out concept
                    For i = 4 To 50000  ''count how many lines for this job (as jobs will always be stacked together this is easy to find
                        If Not ThisWorkbook.Sheets("Downpipe Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Downpipe Machine Batch").Range("A3").Value Then
                         Exit For
                        End If
                    Next i
                           
                           ws.Range("A3:AN" & i - 1).Copy
                           Sheets("Downpipe").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).PasteSpecial Paste:=xlPasteValues

'Call RecordDownpipe
Application.ScreenUpdating = True
End Sub

AND delete Recorddownpipe line and the macro !
 
Upvote 0
and Cleardownpipe can be

Code: 
Sub ClearDownpipeBatch()
Application.ScreenUpdating = False
Dim lr As Long, r As Long, ws As Worksheet
Set ws = Sheets("Downpipe Machine Batch")
lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
With ws
    For r = 4 To lr  ''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:CC" & i - 1).ClearContents
                             
         '''Clear the job that was previously manufactured
         .Range("A3:CC" & i - 1).ClearContents
         '''Sheets("Downpipe Machine Data").Range("A3:Z" & i - 1).Copy
    '''Unique Value indicating Job is in the ready position for NEW JOB - Waiting for Grant's Magic
    .Range("AJ3") = 4
    'End With
End With
Call LoadDownpipe
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Michael M said:
and Cleardownpipe can be

Code: 
Sub ClearDownpipeBatch()
Application.ScreenUpdating = False
Dim lr As Long, r As Long, ws As Worksheet
Set ws = Sheets("Downpipe Machine Batch")
lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
With ws
    For r = 4 To lr  ''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:CC" & i - 1).ClearContents
                             
         '''Clear the job that was previously manufactured
         .Range("A3:CC" & i - 1).ClearContents
         '''Sheets("Downpipe Machine Data").Range("A3:Z" & i - 1).Copy
    '''Unique Value indicating Job is in the ready position for NEW JOB - Waiting for Grant's Magic
    .Range("AJ3") = 4
    'End With
End With
Call LoadDownpipe
Application.ScreenUpdating = True
End Sub
Click to expand...


I just found some code...that does this well!

Code: 
Sub movecompleteddownpipe()Application.ScreenUpdating = False


Dim ws As Worksheet
Set ws = Sheets("Downpipe Machine Batch")
DLR = Sheets("Downpipe Machine Batch").Range("D10000").End(xlUp).Row
WLR = Sheets("Downpipe").Range("D10000").End(xlUp).Row
                ''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
                ''count how many lines for this job (as jobs will always be stacked together this is easy to find
                
                    For i = 4 To 50000
                        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
                                             
                 'Sheets("Downpipe Machine Batch").Range("A3:AN" & DLR).Copy
                Sheets("Downpipe").Range("A" & WLR).Offset(1, 0).PasteSpecial xlPasteValues
                 
                 
                          
Call RecordDownpipe
Application.ScreenUpdating = True
End Sub

Perfect perfect thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Send bulk email attachment error
Replies
3
Views
115
JOHATUP
J
P
  • Question Question
Adding Sort To Current Code
2
Replies
14
Views
250
Pacman52
P
M
  • Question Question
Not running code
Replies
0
Views
218
MagicCarpet
M
M
  • Question Question
Using VBA code in a protected excel sheet
Replies
2
Views
547
MagicCarpet
M
R
  • Question Question
VBA- Adding source and destination cells together rather than overwriting
Replies
4
Views
256
Alex Blakenburg
A

Forum statistics

Threads
1,224,825
Messages
6,181,191
Members
453,021
Latest member
pingpong7117

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