Hi All,
I have a sheet that is running really slow, it was fine when it was created but now takes 20secs to switch between tabs. I have taken as much out of the sheet as I can such as colouring in cells, but its getting worse.
So I can only put it down to the VBA in it, so would someone be so kind and tidy up my basic VBA knowledge:
Code 1:
Code 2:
Code 3:
Any help is much appreciated, and am happy to send the file if needed as cant see where to attached here.
Thanks,
Jamie
I have a sheet that is running really slow, it was fine when it was created but now takes 20secs to switch between tabs. I have taken as much out of the sheet as I can such as colouring in cells, but its getting worse.
So I can only put it down to the VBA in it, so would someone be so kind and tidy up my basic VBA knowledge:
Code 1:
Code:
Private Sub Archive_click_Click()
Dim FileN, FileDir, CurD, TemD, CurWeek, SftN As String
CurWeek = "Week " + CStr(Range("L5").Value)
FileDir = "P:\SAMPLES\Daily Reports\Archive" + CStr(Range("M5").Value) + "" + CurWeek
If Dir(FileDir, vbDirectory) = CurWeek Then
CurD = CStr(Range("C5").Value)
TemD = Left(CurD, 2) + "." + Mid(CurD, 4, 2) + "." + Right(CurD, 2) + " "
SftN = CStr(Range("D5").Value)
FileN = FileDir + "" + TemD + "Samples " + SftN + " Shift Report.xlsm"
Application.DisplayAlerts = False
ChDir FileDir
ActiveWorkbook.SaveAs Filename:=FileN, FileFormat:=52
Application.DisplayAlerts = True
Else
MsgBox "Folder " & CurWeek & " does not exist, can not save file"
End If
End Sub
Code 2:
Code:
Sub import_leg_data()
'
' import_leg_data Macro
'
'Fastcat Bench N1 / Fastcat Bench N2 / Fastcat Bench N4 / Fastcat Bench N6 / Fastcat BMD 2
'Fastcat BMD / Fastcat AID / TP2 Bench 1
'TP2 Bench 2
'TP2 BMD / TP2 AID / WC Prep Area
'Slurry Prep Area / Calcination
Dim TD, TDtemp As Date
Dim CurName, OldName, Istr, MySht(10), TempSht As String
Dim Iret, i As Integer
CurD = CStr(Range("C5").Value)
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
' Enable error handler.
On Error GoTo Error_MayCauseAnError
Dim varCellvalue As String
varCellvalue = Range("g5").Value
'import data
Workbooks.Open Filename:= _
"P:\SAMPLES\Daily Reports\Calcination.xlsm"
ThisWorkbook.Sheets("Calcination").Unprotect Password:="1234"
Range("A1:T92").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("Calcination").Select
Range("A1").Select
ActiveSheet.Paste
Range("B5").Select
Workbooks("Calcination.xlsm").Close
Workbooks.Open Filename:= _
"P:\SAMPLES\Daily Reports\Fastcat AID.xlsm"
ThisWorkbook.Sheets("Fastcat AID").Unprotect Password:="1234"
Range("A1:M65").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("Fastcat AID").Select
Range("A1").Select
ActiveSheet.Paste
Range("B5").Select
Workbooks("Fastcat AID.xlsm").Close
Workbooks.Open Filename:= _
"P:\SAMPLES\Daily Reports\Fastcat Bench N1.xlsm"
ThisWorkbook.Sheets("Fastcat Bench N1").Unprotect Password:="1234"
Range("A1:M65").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("Fastcat Bench N1").Select
Range("A1").Select
ActiveSheet.Paste
Range("B5").Select
Workbooks("Fastcat Bench N1.xlsm").Close
Workbooks.Open Filename:= _
"P:\SAMPLES\Daily Reports\Fastcat Bench N3.xlsm"
ThisWorkbook.Sheets("Fastcat Bench N3").Unprotect Password:="1234"
Range("A1:M65").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("Fastcat Bench N3").Select
Range("A1").Select
ActiveSheet.Paste
Range("B5").Select
Workbooks("Fastcat Bench N3.xlsm").Close
Workbooks.Open Filename:= _
"P:\SAMPLES\Daily Reports\Fastcat Bench N4.xlsm"
ThisWorkbook.Sheets("Fastcat Bench N4").Unprotect Password:="1234"
Range("A1:M65").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("Fastcat Bench N4").Select
Range("A1").Select
ActiveSheet.Paste
Range("B5").Select
Workbooks("Fastcat Bench N4.xlsm").Close
Workbooks.Open Filename:= _
"P:\SAMPLES\Daily Reports\Fastcat Bench N2.xlsm"
ThisWorkbook.Sheets("Fastcat Bench N2").Unprotect Password:="1234"
Range("A1:M65").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("Fastcat Bench N2").Select
Range("A1").Select
ActiveSheet.Paste
Range("B5").Select
Workbooks("Fastcat Bench N2.xlsm").Close
Workbooks.Open Filename:= _
"P:\SAMPLES\Daily Reports\Fastcat BMD 2.xlsm"
ThisWorkbook.Sheets("Fastcat BMD 2").Unprotect Password:="1234"
Range("A1:M65").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("Fastcat BMD 2").Select
Range("A1").Select
ActiveSheet.Paste
Range("B5").Select
Workbooks("Fastcat BMD 2.xlsm").Close
Workbooks.Open Filename:= _
"P:\SAMPLES\Daily Reports\Fastcat BMD 1.xlsm"
ThisWorkbook.Sheets("Fastcat BMD 1").Unprotect Password:="1234"
Range("A1:M65").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("Fastcat BMD 1").Select
Range("A1").Select
ActiveSheet.Paste
Range("B5").Select
Workbooks("Fastcat BMD 1.xlsm").Close
Workbooks.Open Filename:= _
"P:\SAMPLES\Daily Reports\Slurry Prep Area.xlsm"
ThisWorkbook.Sheets("Slurry Prep Area").Unprotect Password:="1234"
Range("A1:W63").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("Slurry Prep Area").Select
Range("A1").Select
ActiveSheet.Paste
Range("B5").Select
Workbooks("Slurry Prep Area.xlsm").Close
Workbooks.Open Filename:= _
"P:\SAMPLES\Daily Reports\TP2 AID.xlsm"
ThisWorkbook.Sheets("TP2 AID").Unprotect Password:="1234"
Range("A1:M67").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("TP2 AID").Select
Range("A1").Select
ActiveSheet.Paste
Range("B5").Select
Workbooks("TP2 AID.xlsm").Close
Workbooks.Open Filename:= _
"P:\SAMPLES\Daily Reports\TP2 Bench 1.xlsm"
ThisWorkbook.Sheets("TP2 Bench 1").Unprotect Password:="1234"
Range("A1:M67").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("TP2 Bench 1").Select
Range("A1").Select
ActiveSheet.Paste
Range("B5").Select
Workbooks("TP2 Bench 1.xlsm").Close
Workbooks.Open Filename:= _
"P:\SAMPLES\Daily Reports\TP2 Bench 2.xlsm"
ThisWorkbook.Sheets("TP2 Bench 2").Unprotect Password:="1234"
Range("A1:M67").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("TP2 Bench 2").Select
Range("A1").Select
ActiveSheet.Paste
Range("B5").Select
Workbooks("TP2 Bench 2.xlsm").Close
Workbooks.Open Filename:= _
"P:\SAMPLES\Daily Reports\TP2 BMD.xlsm"
ThisWorkbook.Sheets("TP2 BMD").Unprotect Password:="1234"
Range("A1:M67").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("TP2 BMD").Select
Range("A1").Select
ActiveSheet.Paste
Range("B4").Select
Workbooks("TP2 BMD.xlsm").Close
Workbooks.Open Filename:= _
"P:\SAMPLES\Daily Reports\WC Prep Area.xlsm"
ThisWorkbook.Sheets("WC Prep Area").Unprotect Password:="1234"
Range("A1:W77").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("WC Prep Area").Select
Range("A1").Select
ActiveSheet.Paste
Range("B5").Select
Workbooks("WC Prep Area.xlsm").Close
ThisWorkbook.Sheets("Fastcat Bench N1").Protect Password:="1234"
ThisWorkbook.Sheets("Fastcat Bench N3").Protect Password:="1234"
ThisWorkbook.Sheets("Fastcat Bench N4").Protect Password:="1234"
ThisWorkbook.Sheets("Fastcat Bench N2").Protect Password:="1234"
ThisWorkbook.Sheets("Fastcat BMD 2").Protect Password:="1234"
ThisWorkbook.Sheets("Fastcat BMD 1").Protect Password:="1234"
ThisWorkbook.Sheets("Fastcat AID").Protect Password:="1234"
ThisWorkbook.Sheets("TP2 Bench 1").Protect Password:="1234"
ThisWorkbook.Sheets("TP2 Bench 2").Protect Password:="1234"
ThisWorkbook.Sheets("TP2 BMD").Protect Password:="1234"
ThisWorkbook.Sheets("TP2 AID").Protect Password:="1234"
ThisWorkbook.Sheets("WC Prep Area").Protect Password:="1234"
ThisWorkbook.Sheets("Slurry Prep Area").Protect Password:="1234"
ThisWorkbook.Sheets("Calcination").Protect Password:="1234"
ThisWorkbook.Sheets("Shift Summary").Protect Password:="1234"
Sheets("Shift Summary").Select
Range("F5").Select
Exit_MayCauseAnError:
Exit Sub
Error_MayCauseAnError:
' Include code here to handle error.
MsgBox "Please check command box details are correct. Otherwise, Leg Reports may not have been created or are still open!"
' Resume execution with exit routine to exit function.
Resume Exit_MayCauseAnError
' End If
End Sub
Code 3:
Code:
Sub Button5_Click()
' StartNewShift Macro
'
'New Report Macro
'Fastcat Bench N1
'Fastcat Bench N3
'Fastcat Bench N4
'Fastcat Bench N2
'Fastcat BMD 2
'Fastcat BMD 1
'Fastcat AID
'TP2 Bench 1
'TP2 Bench 2
'TP2 BMD
'TP2 AID
'WC Prep Area
'Slurry Prep Area
'Calcination
Dim TD, TDtemp As Date
Dim CurName, OldName, Istr, MySht(9), TempSht As String
Dim Iret, i As Integer
Application.ScreenUpdating = False
Application.EnableEvents = False
Istr = "This will clear all data in Shift Report, Continue?"
Iret = MsgBox(Istr, vbYesNo)
If Iret = vbYes Then
TD = Date
Range("C5").Value = TD
Sheets("Shift Summary").Select
ThisWorkbook.Sheets("Shift Summary").Unprotect Password:="1234"
Range("C19:P21,C23:P27,J28,J29,J30,O28:P28,O29:P29,O30:P30,C31:P40,C41:P43"). _
Select
Range("C41").Activate
ActiveWindow.SmallScroll Down:=9
Range( _
"C19:P21,C23:P27,J28,J29,J30,O28:P28,O29:P29,O30:P30,C31:P40,C41:P43,C44:P46"). _
Select
Range("C44").Activate
Selection.ClearContents
Range("F5").Select
ThisWorkbook.Sheets("Fastcat Bench N1").Unprotect Password:="1234"
ThisWorkbook.Sheets("Fastcat Bench N3").Unprotect Password:="1234"
ThisWorkbook.Sheets("Fastcat Bench N4").Unprotect Password:="1234"
ThisWorkbook.Sheets("Fastcat Bench N2").Unprotect Password:="1234"
ThisWorkbook.Sheets("Fastcat BMD 2").Unprotect Password:="1234"
ThisWorkbook.Sheets("Fastcat BMD 1").Unprotect Password:="1234"
ThisWorkbook.Sheets("Fastcat AID").Unprotect Password:="1234"
ThisWorkbook.Sheets("TP2 Bench 1").Unprotect Password:="1234"
ThisWorkbook.Sheets("TP2 Bench 2").Unprotect Password:="1234"
ThisWorkbook.Sheets("TP2 BMD").Unprotect Password:="1234"
ThisWorkbook.Sheets("TP2 AID").Unprotect Password:="1234"
ThisWorkbook.Sheets("WC Prep Area").Unprotect Password:="1234"
ThisWorkbook.Sheets("Slurry Prep Area").Unprotect Password:="1234"
ThisWorkbook.Sheets("Calcination").Unprotect Password:="1234"
Sheets("Calcination").Select
Range("B5,D5,F5,H5,N4:S11,N13:S14,D18:I26,D28:I36,D38:I46,D48:I56,B65:I69,B75:I77,C79:I81,M4:M11,M13:M14").Select
Selection.ClearContents
Range("B5").Select
Sheets("Slurry Prep Area").Select
Range("B5,D5,F5:I7,D18:I23,D25:I30,D32:I37,B46:I50,B56:I58,C60:I62,M4:V5").Select
Selection.ClearContents
Range("B5").Select
Sheets("WC Prep Area").Select
Range("B5,D5,F5:I7,D18:I23,D25:I30,D32:I37,B46:I50,B56:I58,C60:I62,M4:V5,M8:V9,M12:V13,M16:V17,M20:V21,M24:V25,M28:V29,M32:V33").Select
Selection.ClearContents
Range("B5").Select
Sheets("TP2 AID").Select
Range("B5,D5,F5:I5,D20:I27,D29:I31,D33:I33,D38:I40,B50:L54,B60:L62,C64:L66,K21:L21,K23:L23,K25:L25,K27:L27").Select
Selection.ClearContents
Range("B5").Select
Sheets("TP2 BMD").Select
Range("B5,D5,F5:I5,D20:I27,D29:I31,D33:I33,D38:I40,B50:L54,B60:L62,C64:L66,K21:L21,K23:L23,K25:L25,K27:L27").Select
Selection.ClearContents
Range("B5").Select
Sheets("TP2 Bench 2").Select
Range("B5,D5,F5:I5,D20:I27,D29:I31,D33:I33,D38:I40,B50:L54,B60:L62,C64:L66,K21:L21,K23:L23,K25:L25,K27:L27").Select
Selection.ClearContents
Range("B5").Select
Sheets("TP2 Bench 1").Select
Range("B5,D5,F5:I5,D20:I27,D29:I31,D33:I33,D38:I40,B50:L54,B60:L62,C64:L66,K21:L21,K23:L23,K25:L25,K27:L27").Select
Selection.ClearContents
Range("B5").Select
Sheets("Fastcat AID").Select
Range("B5,D5,F5:I5,D19:I26,D28:I30,D32:I32,D36:I38,B48:L52,B58:L60,C62:L64,K20:L20,K22:L22,K24:L24,K26:L26").Select
Selection.ClearContents
Range("B5").Select
Sheets("Fastcat BMD 1").Select
Range("B5,D5,F5:I5,D19:I26,D28:I30,D32:I32,D36:I38,B48:L52,B58:L60,C62:L64,K20:L20,K22:L22,K24:L24,K26:L26").Select
Selection.ClearContents
Range("B5").Select
Sheets("Fastcat BMD 2").Select
Range("B5,D5,F5:I5,D19:I26,D28:I30,D32:I32,D36:I38,B48:L52,B58:L60,C62:L64,K20:L20,K22:L22,K24:L24,K26:L26").Select
Selection.ClearContents
Range("B5").Select
Sheets("Fastcat Bench N2").Select
Range("B5,D5,F5:I5,D19:I26,D28:I30,D32:I32,D36:I38,B48:L52,B58:L60,C62:L64,K20:L20,K22:L22,K24:L24,K26:L26").Select
Selection.ClearContents
Range("B5").Select
Sheets("Fastcat Bench N4").Select
Range("B5,D5,F5:I5,D19:I26,D28:I30,D32:I32,D36:I38,B48:L52,B58:L60,C62:L64,K20:L20,K22:L22,K24:L24,K26:L26").Select
Selection.ClearContents
Range("B5").Select
Sheets("Fastcat Bench N3").Select
Range("B5,D5,F5:I5,D19:I26,D28:I30,D32:I32,D36:I38,B48:L52,B58:L60,C62:L64,K20:L20,K22:L22,K24:L24,K26:L26").Select
Selection.ClearContents
Range("B5").Select
Sheets("Fastcat Bench N1").Select
Range("B5,D5,F5:I5,D19:I26,D28:I30,D32:I32,D36:I38,B48:L52,B58:L60,C62:L64,K20:L20,K22:L22,K24:L24,K26:L26").Select
Selection.ClearContents
Range("B5").Select
Sheets("Shift Summary").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
ThisWorkbook.Sheets("Fastcat Bench N1").Protect Password:="1234"
ThisWorkbook.Sheets("Fastcat Bench N3").Protect Password:="1234"
ThisWorkbook.Sheets("Fastcat Bench N4").Protect Password:="1234"
ThisWorkbook.Sheets("Fastcat Bench N2").Protect Password:="1234"
ThisWorkbook.Sheets("Fastcat BMD 2").Protect Password:="1234"
ThisWorkbook.Sheets("Fastcat BMD 1").Protect Password:="1234"
ThisWorkbook.Sheets("Fastcat AID").Protect Password:="1234"
ThisWorkbook.Sheets("TP2 Bench 1").Protect Password:="1234"
ThisWorkbook.Sheets("TP2 Bench 2").Protect Password:="1234"
ThisWorkbook.Sheets("TP2 BMD").Protect Password:="1234"
ThisWorkbook.Sheets("TP2 AID").Protect Password:="1234"
ThisWorkbook.Sheets("WC Prep Area").Protect Password:="1234"
ThisWorkbook.Sheets("Slurry Prep Area").Protect Password:="1234"
ThisWorkbook.Sheets("Calcination").Protect Password:="1234"
ThisWorkbook.Sheets("Shift Summary").Protect Password:="1234"
End If
End Sub
Any help is much appreciated, and am happy to send the file if needed as cant see where to attached here.
Thanks,
Jamie
Last edited by a moderator: