VBA Excel 2011 Calculate / Clear / Copy

[cyrilbrd]

I have three (3) macro running on the same workbook.
The workbook contains 3 Worksheets.
On the first "USER" I have this macro:
[vba]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")
Do
Set resultCell_R = wks.Range("K22")
Do
rngCalc.Calculate
resultCell_D.Calculate
resultCell_R.Calculate
Loop Until resultCell_D <= 2

Loop Until resultCell_R = 7
End Sub[/vba] 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
[vba]Sub Clear()
Sheet1.Cells.Range("AG16:AK16").ClearContents
End Sub

[/vba] Clears the content in the stated range of Sheet "USER"
No issue here.

The last code
[vba]Dim i As Integer
Sub CopyData()
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("USER").Range("AG16:AZ18").copy

'copy to next page
Sheets("DATA").Select
i = 3
While Range("C" & i).Value <> ""
i = i + 1
Wend
Range("C" & i).Select
Selection.PasteSpecial (xlValues), Transpose:=False
End Sub
[/vba] 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.

OR
[vba] ActiveWorkbook.Sheets("USER").Range("AG16:AZ18").copy

'copy to next page
Sheets("DATA").Select[/vba] 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...



Thanks.
ANY suggestion well appreciated .

[cyrilbrd]

ok solved QUESTION2 with the following:
[vba]Application.ScreenUpdating = False
Windows("workbook1.xlsb").Activate
ActiveWorkbook.Sheets("USER").Range("AG16:AZ18").copy

'copy to next page
Windows("workbook2.xlsb").Activate
Sheets("DATA").Select[/vba]

[mikerickson]

I would fiddle with your Excel Preferences>Calculation and iteration.

[cyrilbrd]

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
[vba] Loop Until resultCell_R = 7 [/vba] 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 Iteration did affect calculations that have nothing to do with the vba.
For instance CSE

=IF(ISBLANK($AG$16:$AK$16)=TRUE,"",IF(COLUMNS(H$2:H7)>COUNTIF(USER!$O:$O,$E7),"",
INDEX(USER!$N:$N,RANDBETWEEN(SMALL(IF(code=$E7,ROW(code)),COLUMNS(H$2:H7)),
LARGE(IF(code=$E7,ROW(code)),COLUMNS(H$2:H7)))))) 

becomes unstable and return values that are not supposed to be generated.