VBA Code Tidy Up - Sheet Running Slow

J

JimUK2

New Member
Joined
Oct 2, 2018
Messages
2
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: 
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:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
The vba you have supplied would not kick in if you were just switching between worksheets.

Usually when something as simple as selecting a cell or switching between sheets taking too long is a result in formula overload or even formatting overload
 
Upvote 0
Thank for the quick reply. Is there a way for me to attach the sheet somewhere for someone to have a look?
 
Upvote 0
You cannot upload files to this site, but you can upload to a share site such as OneDrive, Dropbox, mark for sharing & post the link to the thread.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Locked
  • Question
Help Cleaning Up Code
Replies
2
Views
321
RoryA
RoryA
J
  • Question
VBA - Select file, copy sheets, run code in all sheets
Replies
0
Views
522
jocker_boy
J
willow1985
  • Question
VBA Run time error 1004 stating a protected sheet when not protected??
2
Replies
12
Views
9K
DanteAmor
DanteAmor
albertc30
  • Question
Unusual behavior now code is renaming Sheet Data to Proforma number and saving actual proforma as Create(1)...
Replies
3
Views
334
albertc30
albertc30
P
  • Question
The cell or chart you're trying to change is on a protected sheet. Error upon placing worksheet Change Selection Code
Replies
1
Views
4K
pingme89
P

Forum statistics

Threads
1,214,651
Messages
6,120,744
Members
448,989
Latest member
mariah3

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