cyrilbrd
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")
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 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()
Sheet1.Cells.Range("AG16:AK16").ClearContents
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
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
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
ActiveWorkbook.Sheets("USER").Range("AG16:AZ18").copy
'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...
Thanks.
ANY suggestion well appreciated .
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")
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 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()
Sheet1.Cells.Range("AG16:AK16").ClearContents
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
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
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
ActiveWorkbook.Sheets("USER").Range("AG16:AZ18").copy
'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...
Thanks.
ANY suggestion well appreciated .