Apr 22 2024 04:21 PM
Hi all,
Loving the Ctrl + Shift + V keyboard shortcut; however, I can't get it to stop recalculating the data tables in my open workbooks.
I have my calculation settings set to "Automatic Except For Data Tables" and 'Recalculate workbook before saving unchecked." Is there some setting that I am forgetting or a workaround?
I've included screenshots of what I think are the relevant settings.
Thanks!
Apr 22 2024 06:29 PM
Under the "Calculation options" section (like you already mentioned), you'll find a dropdown menu labeled "Workbook calculation."
Manual: Excel only calculates formulas when you manually instruct it to do so by pressing F9 or using the "Calculate Now" button (Shift + F9).
By selecting the "Manual" option in the "Workbook calculation" dropdown menu, you can activate manual recalculation mode. In this mode, Excel will only recalculate formulas when you specifically request it, either by pressing F9 or using the "Calculate Now" button.
If you want to completely stop the recalculation of data tables in Excel, regardless of the settings or conditions, you can consider using a VBA (Visual Basic for Applications) macro to temporarily suspend calculation while you perform your tasks.
Here's a simple VBA macro that you can use to turn off automatic calculation:
Vba codes are untested, please backup your file first.
Sub TurnOffCalculation()
Application.Calculation = xlCalculationManual
End Sub
You can run this macro by following these steps:
After running this macro, Excel will stop automatically recalculating data tables. However, keep in mind that this will also prevent the recalculation of all other formulas in your workbook until you turn calculation back on.
To turn calculation back on, you can create another macro:
Sub TurnOnCalculation()
Application.Calculation = xlCalculationAutomatic
End Sub
Follow the same steps as above to create and run this macro when you want to resume automatic calculation.
Please note that manually controlling calculation settings via VBA macros can have implications for the functionality and accuracy of your workbook, so use these macros cautiously, they have impact on your specific scenario. The text, steps and code were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
Apr 23 2024 10:46 AM
@NikolinoDE Thank you for the tips and code. I was hoping there was a way to perform the tasks without having to use manual calculation mode.
I would think that regardless of what shortcut you use to paste values, Ctrl + Shift + V or Alt + E + S + V, they should not override the calculation mode. I'm not sure why, but the data tables only periodically recalculate when using Ctrl + Shift + V even though my calculation mode is set to "Automatic Except For Data Tables."
Since I may have multiple workbooks open at a time, it starts refreshing 20-30 Data tables and is painfully slow.
One solution I was thinking about is writing a macro to paste values from the clipboard and then using the Application.Onkey Method to bind the macro to Ctrl + Shift + V. However, then it's a macro and there is no undo.