Whole Project - Speed Up Possible?

[Lukums]

Hello everyone,

I've turned to forums again for some final assistance.

First off thank you for everyone over the last 2 years who had helped me on this automation project.
We are finally finished. However one final things remains.

I'm not a programmer or developer in the sense of those words but the project I've written seems clunky.

Does anyone know of a service like "paid" work to have a look into the project and make smart changes to run smoother?
Essentially not changing the fundamentals obviously, but shortening and making those smart choices in relation to the coding.

Here's a snip so you guys have some idea, any guidance on this one would be great!.

------------------------


Sub Ridge400Engage()


Sheets("Ridge 400 Machine").Select


'Due to finding decimal places needed to set sheet to general upon manufacturing
Cells.Select
Selection.NumberFormat = "General"


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


For i = 3 To LastRow


'P3 = CLEAR JOB PUSH
'BR3 = LOAD NEW JOB
'BT3 = START
'Q3 = JOB COMPLETED
'BU3 = FLAG JOB COMPLETED 2
'BS3 = GRANTS MAGIC


If ActiveSheet.Range("R3").Value = "Job Completed" And Range("BR3").Value = "1" And Range("P3").Value <> "1" And Range("BT3").Value = "0" Then

'FLAGS COMPLETED JOB WITH UNIQUE VALUE TO MOVE
Range("BU3").Select
ActiveCell.FormulaR1C1 = "2"


Rows(i).Select
Selection.Copy


Sheets("Ridge 400 Machine").Select


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


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

''' if this is the last job then it remains in row 1 while the entire process runs until this workbook is selected to find the value "2" in BU3 for permanent deletion
'''Explaination of 3:3 select and delete shift

'Next JOB Load UP
Rows("3:3").Select
Selection.Delete Shift:=xlUp

' WONT RUN AGAIN UNTIL GRANT GIVES ME 1 IN GRANTS MAGIC

Range("BT3").Select
ActiveCell.FormulaR1C1 = "1"

'''''

'QTY WRITE TO 0 - PLC PARAMETER - CLEARING ALL VALUES WITHHELD
Range("P3").Select
ActiveCell.FormulaR1C1 = "1"

'Application.Wait (Now + TimeValue("0:00:01"))


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''
'''
'''
'Ridge 400 Machine'

'''Register D190'''
'QTY ROLLED AND SCRAP OR '''1'''
Sheets("Formuals").Select
Range("O3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("O3").Select
ActiveSheet.Paste
'''''''''''
'Register D582'

'Change STATUS CONTROL - does QTY + SCRAP = QTY ROLLED? '''2'''
Sheets("Formuals").Select
Range("N3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("N3").Select
ActiveSheet.Paste
'''''''''''''


'Job Completed Check '''3'''
Sheets("Formuals").Select
Range("R3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("R3").Select
ActiveSheet.Paste

''''''''''''


'Colour Check '''4'''
Sheets("Formuals").Select
Range("Q3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("Q3").Select
ActiveSheet.Paste


''''COIL CHANGING LOGIC - CAPTURE'''''
'Stock Code 1st Coil Entry '''5'''
Sheets("Formuals").Select
Range("AQ3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("AQ3").Select
ActiveSheet.Paste


'Length from BOM 1st Coil Entry '''6'''
Sheets("Formuals").Select
Range("AR3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("AR3").Select
ActiveSheet.Paste

'Coil Sheet 2 QTY Remaining after Coil Sheet 1 '''7'''
Sheets("Formuals").Select
Range("BL3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("BL3").Select
ActiveSheet.Paste

'Stock Code 2nd Coil Entry '''8'''
Sheets("Formuals").Select
Range("BJ3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("BJ3").Select
ActiveSheet.Paste

'Length from BOM 2nd Coil Entry '''9'''
Sheets("Formuals").Select
Range("BK3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("BK3").Select
ActiveSheet.Paste

'Coil Change Mid Job Done or Not? If Not Grab value from D135 and paste into QTY Coil Sheet 1 on the new dailyrecorded '''10'''
Sheets("Formuals").Select
Range("BV3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("BV3").Select
ActiveSheet.Paste


'''Wasteage / Total Usage / Total Weight'''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Coil Sheet 1, Wastage Total in (LM) 11
Sheets("Formuals").Select
Range("AT3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("AT3").Select
ActiveSheet.Paste

'Coil Sheet 1, Total Usage (LM) 12
Sheets("Formuals").Select
Range("AU3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("AU3").Select
ActiveSheet.Paste

'Coil Sheet 1, Total Weight (kg)13
Sheets("Formuals").Select
Range("AV3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("AV3").Select
ActiveSheet.Paste

'Coil Sheet 2, Wastage Total in (LM) 11
Sheets("Formuals").Select
Range("BM3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("BM3").Select
ActiveSheet.Paste

'Coil Sheet 2, Total Usage (LM) 12
Sheets("Formuals").Select
Range("BN3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("BN3").Select
ActiveSheet.Paste

'Coil Sheet 2, Total Weight (kg)13
Sheets("Formuals").Select
Range("BO3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("BO3").Select
ActiveSheet.Paste

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Date Time NOW - Coil Sheet 1 '''14'''
Range("AJ3").Select
Range("AJ3") = Date



'Date Time NOW - Coil Sheet 2 '''15'''
Range("BC3").Select
Range("BC3") = Date
'''''END COIL LOGIC - CAPTURE''''''

End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'''
'''



If Range("BS3").Value = "1" Then


Range("P3").Select
ActiveCell.FormulaR1C1 = "0"

Range("BT3").Select
ActiveCell.FormulaR1C1 = "0"

End If

'''
'''



Next i


Call SaveRidge400


End Sub




Sub SaveRidge400()


Sheets("Ridge 400 Machine").Select


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


For i = 3 To LastRow


If Range("R" & i).Value = "Job Completed" And Range("BU" & i).Value = "2" Then
Rows(i).Select
Selection.Copy


'''WARNING LUKE YOU CHANGED THIS ON THE 26082016 TO **** AROUND WITH COMBINING ALL PROCESSES CHANGE THIS BACK WHEN YOU'RE DONE! DON'T USE THIS FOR PRODUCTION!!!!!''''
Workbooks.Open Filename:="C:\Users\luke\Desktop\RecordedDailyJobs.xlsm"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim p As Integer, q As Integer


p = Worksheets.Count


For q = 1 To p


Next q


Sheets("Sheet1").Select

'''' begin the process of entering on main page, as well as splitting to the corresponding sheets, we also then want to select the coil range where required and paste
'''' this on the relevant sheets in H drive to be recorded by accounts''''


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


ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
If Range("R" & i).Value = "Job Completed" And Range("BU" & i).Value = "2" Then
Rows(i).Select
Selection.ClearContents
End If
End If
Application.CutCopyMode = False




''
Next i


Call Ridge300CoilCheck


End Sub

 

[FDibbins]

You really should use code tags when posting code

 

[Michael M]

Without looking through the entire code, you could speed things up by removing almost all of the Select.Selection codes
for example...this

Sheets("Formuals").Select
Range("AT3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("AT3").Select
ActiveSheet.Paste

could be reduced to

Sheets("Formuals").Range("AT3").Copy Sheets("Ridge 400 Machine").Range("AT3")

you could also put at the start of the code

Application.screenupdating=False

and at the end of the code

Application.screenupdating=true

AND
when posting code, can you please use code tags....it makes it easier to debug and format

 

[Michael M]

Also why do you have a for / next loop in the ridge400 macro, when it doesn't appear to do anything ???

 

[Lukums]

Without looking through the entire code, you could speed things up by removing almost all of the Select.Selection codes
for example...this

Sheets("Formuals").Select
Range("AT3").Select
Selection.Copy
Sheets("Ridge 400 Machine").Select
Range("AT3").Select
ActiveSheet.Paste

could be reduced to

Sheets("Formuals").Range("AT3").Copy Sheets("Ridge 400 Machine").Range("AT3")

you could also put at the start of the code

Application.screenupdating=False

and at the end of the code

Application.screenupdating=true

AND
when posting code, can you please use code tags....it makes it easier to debug and format

Hello Michael,

Thanks for responding so fast!

Yes I'll be sure to use tags in the future sorry about this.

I see what you mean by selecting I'm sure this will speed it up ten folds, seeing as this process is replicated 10 times.

I'll also take a closer look within my code to ensure processes are removed that aren't doing anything.
I have a really bad habit of commenting things out when I'm testing/not using then forgetting to re-commenting it back out.

Is there any other way to have color writting in VBA? Would be super helpful!

Luke

 

[Michael M]

AND
you don't need to requote other posts in your replies, UNLESS you have made changes to that quote !!
There are options for changing text colors in the VBE
Tools>>Options>>Editor Format.....but they are very basic.
Might be a good idea to comment out stuff as soon as you are finished with it, that way it turns green in color!!