Hello,
Is there a way that I can set a single worksheet in a workbook to always be on manual calculation, but keep all other sheet in the workbook set to automatic?
I want to be able to open the workbook, any calculations to perform automatically and then i will select the 'manual' sheet and perform these calculations manually. I also need to these settings to always apply each time i open the workbook.
Thanks!
Last edited by JONNY981; 04-24-2009 at 05:09 AM.
Right click the Excel icon next to the File menu, View Code, and paste:
Please Login or Register to view this content.
Last edited by Kenneth Hobson; 04-22-2009 at 08:18 AM.
One of the other guys/gals will have a better answer but no I'm not aware of the ability to do that - you can disable calculation for given sheets on the workbook being opened and then run a separate routine to calculate a given sheet, eg:
So to calculate Sheet2...Please Login or Register to view this content.
The above could be assigned say to a keyboard shortcut ... you could add some checks to the routine to ensure the ActiveSheet name is valid for the exercise (prior to disabling the Calculation)Please Login or Register to view this content.
When doing anything calculation related be sure to check out Charles Williams' site: http://www.decisionmodels.com/calcsecrets.htm
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Bad solution
Last edited by foxguy; 04-21-2009 at 12:42 PM. Reason: bad solution
bad solution
Last edited by foxguy; 04-21-2009 at 12:42 PM. Reason: bad solution
foxguy, my proposal does not alter Calculation from it's current state at Application level, the sheets in question are simply disabled in a calculation sense and thus never calculated, they could only be calculated by invoking the manual routine (akin to SHIFT+F9 in a traditional sense)....
(nor will the offending sheets be calculated on workbook open)
DonkeyOte, looks like you're right. I misread your message. EnableCalculation is a new property for me. I read it as Calculation.
Johnny, ignore my previous post, looks like DonkeyOte's is the better solution.
Thanks everyone,
I tried Kenneth Hobson's solution first, which seemed like exactly what I wanted to do, but excel gave me a run time error '424'.
I then tried DonkeyOte's solution and it seems to work perfectly, thanks!
I fixed my code so it should work for you if your sheet for manual calculation is named Manual.
I have encountered a problem with your code DonkeyOte, excel is giving me a syntax error for the following (but with my sheet name):
I have tried changing the name of the sheet etc but no luck, havent a clue why this is happening as it worked fine at first!Please Login or Register to view this content.
I was using CodeName... if you use the tab name
Please Login or Register to view this content.
Thanks, looking over that now ive realised that was a really stupid question lol!
I'm using DonkeyOte's macro as such:
Private Sub Workbook_Open()
Sheets("Summary").EnableCalculation = False
End Sub
This is working fine, but it doesn't automatically work when I open the file. Any idea why?
Excel 2007
Macro Daddy, welcome to the board, however, please note:
Your post does not comply with Rule 2 of our Forum RULES.
Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
(also - please use CODE tags when posting VBA)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks