Copy Tab into new work book with Cell references outside sheet

[Kemidan2014]

I have a workbook that uses a macro upon opening which lately is taking alot of time processing
i dont think its the macro itself bogging down the spreadsheet but a New tab i added that has 370 Sumifs and Countifs formulas and 9 graphs for various Data analysis that management wants to review weekly
it did not start this long opening until after i completed this worksheet addition.

My thought was "Why dont i make this tab its own seperate workbook and remove it from the original" So i Copied the sheet into a new workbook and saved it but when you close and reopen this newly saved workbook, all the formulas change to #Value Error. but i checked the formulas and the File links are still valid. Do i really have to have BOTH workbooks open in order for this NOW seperated sheet to work?

What am i missing in the order that i did things?

 

[Alex Blakenburg]

You might need to show us your spreadsheet. Do you need that many SumIfs and Countifs or could you perhaps use a Pivot Table to do the accumulation ?
Are you using whole column referencing ?

You can't use SumIfs and CountIfs on a closed workbook.
Functions that Can Access Closed Workbooks

 

[Kemidan2014]

As i cant figure out how to delete a post, I have determined that the Formulas are not the cause of my spreadsheet taking so long to open. I found the code that can turn off calculations while macro is running and that did not resolve anything. I will have to continue to investigate. Thank you for your response =)

 

[Alex Blakenburg]

Thanks for the update. My understanding is that turning off calculations in a Workbook open event is too late and the calculation will happen before the event is executed.
This is discussed here:
turn off Auto-calculation when launching an xls file
And provides a reference link to:
Controlling Excel Calculation - Decision Models

Have you run through the basics. Things that slow the Excel workbook down include:
• Does Ctrl+End on each sheet take you way paste the end of the data
• Are you using whole column referencing A:M instead of A1:M1000
• Are you using conditional formatting on thousands or rows
• Does the workbook have links to external sources
• Are you using volatile functions such as Offset & Indirect.

 

[Kemidan2014]

To answer your questions,

First heres the code

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

Does Ctrl End take me past data - Right now it does not
Am i using whole column referencing: I am not sure if you are meaning in my macro code or in my formulas. but for the formulas yes because this spreadsheet will grow over the years
Am i using alot of conditional formatting: I am not using conditional formatting in the target sheet. I am on another sheet but only 50x12 range of data
Does work book have links: yes it needs it but the formulas in my work book do not reference outside information.
I am not using Offset or Indirect

Additionally i went ahead and ran just the code above and the same amount of time took place.

 

[Alex Blakenburg]

i dont think its the macro itself bogging down the spreadsheet

Not sure I agree. Depending on the no of rows that macro is doing a lot of work including having to open a 2nd workbook.
Try commenting the macro out and see if it makes much of a difference.

Also SumIfs & Countifs using whole column referencing do a lot of work.
Ideally keep your data in tables and use table referencing in your formulas, that way the formulas still expand as you add data.
If you can't do that pick a theoretical maximum no of rows, even if that runs into 10s of thousands that is still better than the million rows referenced when you reference the whole column.
If commenting out the macro doesn't make a huge difference. Take a copy of your workbook and copy the formulas as values and see if the performance improves dramatically