Paste Value not working - VBA stop calculation issue? [SOLVED]

[lzhenl]

Hi,


Below is a VBA code that I have to stop calculation for Worksheet A.


Option Explicit

Private Sub Worksheet_Activate()

Application.Calculation = xlCalculationManual

End Sub

Private Sub Worksheet_Deactivate()

Application.Calculation = xlCalculationAutomatic

End Sub


However, I realise that I will not be able to copy and paste data from Worksheet B to Worksheet A. Worksheet B does not have this VBA code.

Do I have to enter another VBA code to enable the copy and paste function?


Warmest regards, Zhen

 

[SirJB7]

Hi, lzhenl!

As those 2 procedures respond to worksheet events activation and deactivation, yes, you should place them in all the worksheets that you want to behave in that way.

Unless you want to do it for all worksheets of a workbook, in this case you should place similar code lines for the events:

Workbook_Activate & Workbook_Deactivate

in the object section code of ThisWorkbook instead of in each object code section of each worksheet.

Regards!

 

[Hui]

Zhen


I would change the Deactivate subroutine as shown below:

[pre]

Private Sub Worksheet_Deactivate()
Application.Calculation = xlAutomatic
Calculate
End Sub

[/pre]

the calculate lines forces a recalculate right now

 

[jeffreyweir]

Reading Izhenl's post, I think the issue is that the Worksheet_Activate event clears the clipboard, meaning he can't paste something that he's just copied on the other sheet.

 

[jeffreyweir]

Izhenl - I believe the only way around this is to write some code that prompts the user for the copy range and the paste range, and then transfers the data directly from one to the other without using the Excel clipboard.


Another solution is to copy the data into another office program first - like WORD - because - unlike the Excel clipboard - the office clipboard isn't cleared by code like this.


But both of those solutions are pretty tedious. The best solution is to not turn off calculation in the first place. Is there a particular reason why you are changing the calculation to manual?


Note that many people think that having to run a spreadsheet in manual calculation mode is simply a byproduct of having a big spreadsheet with lots of data. But in actual fact in most cases I've seen, this is usually due to bad/inefficient spreadsheet design. The worst culprit is volatile functions such as OFFSET or INDIRECT or TODAY or NOW that are referenced by tens of thousands of 'downstream' formulas.


Do you make use of these functions in your workbook? If so, there are alternatives for them, that will greatly decrease the time you spend waiting for Excel to recalcualte.


You can read more on how to avoid these functions at http://msdn.microsoft.com/en-us/library/office/ff726673%28v=office.14%29.aspx


Furthermore, manual calc mode is dangerous, because it is very easy for users to change model inputs but forget to fully recalculate the sheet, meaning output is incorrect.

 

[lzhenl]

Dear SirJB7 and Hui and jeffreyweir


Many thanks for your replies. The main reason I turn off the calculation for this worksheet is because of the huge amount of data and formulas in this worksheet, which means when I copied and paste data, Excel takes some time to re-calculate the cells.


The main functions I use for this worksheet is VLOOKUP plus some conditional formatting, which I now think shouldnt cause Excel to take long to do the calculation.

Will take your advice and recheck my file again see if there is other functions which I may have overlook.


Thanks again all for your help!

 

[jeffreyweir]

Cool. I suggest you simply do a 'Find All' search (Cntl + F) on OFFSET and INDIRECT etc within your worksheet and see how may hits you get. Make sure you select 'within workbook' and 'look in formulas' and then hit the 'Find All' option.


The other thing to look out for is that you are not copying large ranges throughout the worksheet. For instance, the other day I saw a spreadsheet that copied a big table of around 15000 rows times 11 columns to multiple places throughout the workbook without good reason. In all, there were 1.4 million formulas involved in duplicating that block throughout the spreadsheet, for no good reason. I restructured it so that aggregation formulas (e.g. SUMIF, SUMPRODUCT etc) were pointed directly at the raw data.


Some other things to check:

* you say you have lots of VLOOKUPS. VLOOKUPS are MUCH MUCH faster if your data is sorted, and you set the last argument to TRUE (although you need to read http://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/ for more info and some gotchas).

* If you use SUMPRODUCT a lot, see if you can replace them with SUMIFS (new to EXcel 2007) because its faster.

* Don't use =IF(ISERROR(SomeFormula), SomeOtherFormula, SomeFormula), because both branches of the IF get evaluated even if only one of them is ultimately used. Instead, use the new IFERROR function. =IFERROR(SomeFormula,SomeOtherFormula). It's much simpler, and only requires half the amount of processing.

* Don't use IF statements like =IF(F1=0,"",F1) to replace zeros with blanks, because this suppression requires significant overhead to do something that you can accomplish with no overhead whatsoever via custom number formats.

So instead of something like this:

=IF(F1=0,"",F1)

… we could just use this:

= F1

…in conjunction with the following custom number format:

#,##0.0;- #,##0.0;;

Do a google search on Custom Number Formats for more info, or check out http://peltiertech.com/Excel/NumberFormats.html or http://www.corality.com/tutorials/custom-number-formats-excel


And finally, read and reread that link above (i.e. http://msdn.microsoft.com/en-us/library/office/ff726673%28v=office.14%29.aspx ).


Post back if you have any more questions.


Good luck