I have a large excel workbook that has 23 sheets currently. The workbook takes about 100k lines of inventory and sorts it into 8 of the worksheets by inventory location, the other 15 sheets are used to tally numbers and move inventory as need. The 100k inventory is sorted by the 8 inventory sheets using around 400 sumifs and countifs formulas per sheet totaling around 3200 formulas. My workbook is starting to get serious lag as every time data is changed on one of the 15 working sheets the 8 inventory sheets recalculate. To breakdown what I am trying to find out, I need 15 sheets set to auto-calculate and 8 sheets set to manual calculate at the same time. Is there anyway to do this. I have not included any code as this is a excel functionality question. I have searched extensively but all the literature I can find says that with the calculate function it is a all or nothing situation. But before rewriting the entire workbook to switch all the formula to VBA I thought I would see if anyone has found a work around.

CodePudding user response：

you will need vba to do this.

1. disable auto-calculation and calculate on save (can be done in excel or vba with Application.Calculation = xlManual, Application.CalculateBeforeSave = False)
2. make a list of all the sheets you want to "auto-calculate"
3. loop through the list, use Worksheets(1).Calculate or Sheets("Sheet1").Calculate to calculate a single sheet at a time.
4. You can set this to run every time you make a change in your notebook with the Worksheet.Change event, or personally I would make a button that runs the macro when clicked.

quick prototype

Sub customcalc
Dim myarray As Variant
myarray = Array("Sheet1", "Sheet2", "Sheet3")
For Each s In myarray
Sheets(s).Calculate
Next s
End Sub