Results 1 to 4 of 4

Thread: VBA Excel 2011 Calculate / Clear / Copy

  1. #1

    VBA Excel 2011 Calculate / Clear / Copy

    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")
    Set resultCell_R = wks.Range("K22")
    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()
    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

    '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
    [/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.

    [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...

    ANY suggestion well appreciated .
    Last edited by cyrilbrd; 10-04-2012 at 12:48 AM.

  2. #2
    ok solved QUESTION2 with the following:
    [vba]Application.ScreenUpdating = False

    'copy to next page

  3. #3
    Mac Moderator VBAX Guru mikerickson's Avatar
    May 2007
    Davis CA
    I would fiddle with your Excel Preferences>Calculation and iteration.

  4. #4
    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
    PHP Code:
    becomes unstable and return values that are not supposed to be generated.
    Last edited by cyrilbrd; 10-04-2012 at 06:26 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts