Improving Workbook Macro "On Open" Speed

[Kemidan2014]

So as I have been working on an excel "database" that's used to log and keep track of customer complaints after I had added a sheet to it that has 440 formulas I noticed the spreadsheet takes about 30seconds to a minute to open.

I feel like the formulas are what's bogging it down because as the macro is running and adding new data from a separate master file its recalculating

Does Application.Calculation := xlmanual apply to the WHOLE workbook or only to active sheets? I experimented with adding this into my macro that I have set up for when workbook is opened but I saw no discernable effect.

I activated the COMs Addin Inquire to run workbook analysis but it wasn't what I was expecting it to be so I am not sure how to decipher the information it has

I already have
Application.Screenupdating=false
Application.Calculation:=xlmanual

Are there any other suggestions or things I should look for that I should review to try and improve efficiency of this opening macro?

 

[GlennUK]

Application.Calculation := xlmanual applies to the whole application, not just specific workbooks or sheets. Show your code, as that may give a clue as to what is causing the slowness.

 

[Kemidan2014]

Private Sub Workbook_Open()
  Dim srcSH As Worksheet, desSH As Worksheet
  Dim i As Long, j As Long, nRow As Long, n As Long
  Dim rng As Range, col As Range, c As Range, f As Range
 
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Sheets("Complaints").Unprotect Password:="Secret"
  Workbooks.Open ("O:\1_All Customers\Current Complaints\ToyotaData.xlsx")
  Set srcSH = Workbooks("ToyotaData.xlsx").Sheets("Data")
  Set desSH = Workbooks("Customer Complaint Tracker.xlsm").Sheets("Complaints")
  Set rng = srcSH.Range("A:B,D:E,H:K,O:O,Q:AB,AH:AH")
 
  For Each c In srcSH.Range("A2", srcSH.Range("A" & Rows.Count).End(3))
    Set f = desSH.Range("A:A").Find(c.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then nRow = f.Row Else nRow = desSH.Range("A" & Rows.Count).End(3).Row + 1
    j = 0
    For Each col In rng.Columns
      n = col.Column
      j = j + 1
      desSH.Cells(nRow, j).Value = srcSH.Cells(c.Row, n).Value
    Next
  Next
  srcSH.Parent.Close False
  Sheets("Complaints").Protect Password:="Secret"
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
End Sub

THis is the code i have set up for opening. its designed to look at a seperate workbook that we download weekly to update new information into a worksheet. this sheet doesnt grow that much in aweek

 

[Kemidan2014]

OK so just one extra bit of information i ran that section of the code by itself and it took the same amount of time.

 

[GlennUK]

You include a line that opens another workbook:

Workbooks.Open ("O:\1_All Customers\Current Complaints\ToyotaData.xlsx")

... so, what if that is what is taking the time?