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
Michael M said:
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 !
Click to expand...

Urgent one @Michael M
The Downpipe where the data is heading is pasting over the LAST value on this destination sheet NOT the next Blank Row in column A.
By design? I can't see an issue. Can you point it out?

ws.Range("A3:AN" & i - 1).Copy
Sheets("Downpipe").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).PasteSpecial Paste:=xlPasteValues
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Change to

Code: 
Sheets("Downpipe").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row [color=red]+ 1[/color]).PasteSpecial Paste:=xlPasteValues
 
Upvote 0
Michael M said:
Change to

Code: 
Sheets("Downpipe").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row [COLOR=red]+ 1[/COLOR]).PasteSpecial Paste:=xlPasteValues
Click to expand...


No idea what's happened - but --
Sheets("Barge").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteValues '''isn't pasting the copy does happen but not the paste
Code: 
Sub movecompletedBarge()Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = Sheets("Barge Machine Batch")
                ''Move the job to daily recorded sheet
                 'ThisWorkbook.Sheets("Barge Machine Batch").Range("B6").Value = ThisWorkbook.Sheets("Barge Machine").Range("B6").Value  ''AS B2 in Data sheet is always going to be current First in First out concept
                    For i = 4 To 50000  ''count how many lines for this job (as jobs will always be stacked together this is easy to find
                        If Not ThisWorkbook.Sheets("Barge Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Barge Machine Batch").Range("A3").Value Then
                         Exit For
                        End If
                    Next i
                           
                           ws.Range("A3:AN" & i - 1).Copy
                           Sheets("Barge").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteValues
                      
Call ClearBargeBatch
Application.ScreenUpdating = True

End Sub

Then...

Code: 
Sub ClearBargeBatch()Application.ScreenUpdating = False


Dim lr As Long, r As Long, ws As Worksheet
Set ws = Sheets("Barge 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("Barge Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Barge Machine Batch").Range("A3").Value Then
            Exit For
         End If
    Next r
                             
    .Range("A3:CC" & r - 1).ClearContents
    .Range("AJ3") = 4
End With


Call LoadBarge
Application.ScreenUpdating = True
End Sub

Is giving me a range defined error on:

If Not ThisWorkbook.Sheets("Barge Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Barge Machine Batch").Range("A3").Value Then

Sorry Michael I don't know why this is.
 
Upvote 0
UPDATE:

The Clear section is fine again (mucked around a bit and got it working)
The biggest issue now seems that:

Sheets("Barge").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteValues

Won't paste on the barge sheet where the last empty row in column a

It enters into this line to say "YEP" but nothing happens and continues on.
 
Upvote 0
Are you sure "Barge" is the correct sheet name.....no spaces leading or trailling ??


My error on the next one....I always use r as as a row variable....change it back to i
Code: 
With ws
    For [color=red]i[/color] = 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("Barge Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Barge Machine Batch").Range("A3").Value Then
            Exit For
         End If
    Next [color=red]i[/color]
 
Upvote 0
Michael M said:
Are you sure "Barge" is the correct sheet name.....no spaces leading or trailling ??


My error on the next one....I always use r as as a row variable....change it back to i
Code: 
With ws
    For [COLOR=red]i[/COLOR] = 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("Barge Machine Batch").Range("A" & i).Value = ThisWorkbook.Sheets("Barge Machine Batch").Range("A3").Value Then
            Exit For
         End If
    Next [COLOR=red]i[/COLOR]
Click to expand...

All good caught that one also -

100% sure it looks like it IS copying but it's overwriting what's already there not truly finding last row which is empty
 
Upvote 0
Are you sure the last row is defined by column "A" in the Barge sheet and another column isn't longer ??

Code: 
Sheets("Barge").[color=red]Range("A" & Cells(Rows.Count, "A")[/color].End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteValues
 
Last edited:
Upvote 0
Michael M said:
Are you sure the last row is defined by column "A" in the Barge sheet and another column isn't longer ??

Code: 
Sheets("Barge").[COLOR=red]Range("A" & Cells(Rows.Count, "A")[/COLOR].End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteValues
Click to expand...

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

I messed this up bad didn't I...
 
Upvote 0
UPDATE:

So...If you step through it while LOOKING at Barge sheet is works perfectly.

If I'm on a different sheet stepping through it does paste BUT it overwrites what's currently there! I've never seen such things happen
 
Upvote 0
This doesn't determine what is the lastrow in Sheets Barge

Code: 
Set ws2 = Sheets("Barge")
                        Lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
                        Sheets("Barge").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).PasteSpecial Paste:=xlPasteValues
you need to use

Code: 
Set ws2 = Sheets("Barge")
                        Lastrow = [color=red]ws2[/color].Cells(Rows.Count, "A").End(xlUp).Row + 1
                        Sheets("Barge").Range("A" & Lastrow).PasteSpecial Paste:=xlPasteValues
 
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,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

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