View Full Version : VBA Excel 2011 Calculate / Clear / Copy

10-03-2012, 08:17 PM
I have three (3) macro running on the same workbook.
The workbook contains 3 Worksheets.
On the first "USER" I have this macro:
Sub reload()

Dim randCell As Range, rngCalc As Range, resultCell As Range
Dim wks As Worksheet
Set wks = ThisWorkbook.Sheets("USER")
Set rngCalc = wks.Range("A1:BD40")
Set resultCell_D = wks.Range("Q24")
Set resultCell_R = wks.Range("K22")
Loop Until resultCell_D <= 2

Loop Until resultCell_R = 7
End Sub I do not want "calculate" to be automatic but controlled by the user.
The vba so far works fine. It so linked to a button.
This said it blocks the two other sheet to calculate... mean to say I must either shut down the vba (counter productive) or press F9, which in turn recalculate all sheets :)

QUESTION1: What must be modified in the above code to control the calculation on Sheet "USER" only.

The second code
Sub Clear()
End Sub

Clears the content in the stated range of Sheet "USER"
No issue here.

The last code
Dim i As Integer
Sub CopyData()
Application.ScreenUpdating = False

'copy to next page
i = 3
While Range("C" & i).Value <> ""
i = i + 1
Range("C" & i).Select
Selection.PasteSpecial (xlValues), Transpose:=False
End Sub
Copies the range of interest from "USER" into "DATA"
This vba works fine as it will copy below the previously copied range.
This said the copy / pastevalues should not induce calculate hence the Application.ScreenUpdating = False

BUT this throw off vba 1 completely.
The application is unable to perform calculation as I suspect that both codes (1 and 3) act against each other.

QUESTION2: How to be able to restrain the calculation in "USER", be able to copy pastevalue into "DATA" without having the whole workbook to freeze.


'copy to next page
Sheets("DATA").Select That bit refers to one (1) workbook, with two sheets.
What is the best way to use that macro (that bit) and make reference to two (2) different workbook?
USER in workbook1.xlsb
DATA in workbook2.xlsb
Both located in Macintosh HD/Users/Mach7/Desktop

All references I found use the Windows c:/ path...

ANY suggestion well appreciated .

10-04-2012, 12:47 AM
ok solved QUESTION2 with the following:
Application.ScreenUpdating = False

'copy to next page

10-04-2012, 06:55 AM
I would fiddle with your Excel Preferences>Calculation and iteration.

10-04-2012, 05:45 PM
Thanks, Calculation was set to Manual, but I didn't play with the iteration, I'll have a go.
This said the first book (1)Sheet (USER) is giving me some concerns. The macro would freeze the whole excel at times. I feel that this
Loop Until resultCell_R = 7 did throw off the calculation and is somehow draining the system. It worked fine without this added piece but this is also needed so...

Thanks for the feedback
I'll keep the forum posted if I find a solution.

mikerickson (http://www.vbaexpress.com/forum/member.php?u=10706) Iteration did affect calculations that have nothing to do with the vba.
For instance CSE

LARGE(IF(code=$E7,ROW(code)),COLUMNS(H$2:H7)))))) becomes unstable and return values that are not supposed to be generated.