How do I get VBA to Copy a range of cells, WAIT FOR THE CELLS TO CALCULATE and paste in another range?

Question

This code will go into the sheet and switch out a cell to a certain function that is linked to the range I am going to copy. Then it will pastevalues on another sheet in a specific cell. I change out ActiveCell (Line 6) with each copy and paste. This code isn't waiting for the cells that will be copied to calculate. Therefore I have the same cell values in my whole worksheet. Any help would be great :) I tried the "Application.Calculate" and that didn't work. This code goes on to copy and paste 100 different tickers for stocks I included five series of codes, but they keep going on to record each stock price.

Sheets("Investing").Select
    ActiveWindow.SmallScroll Down:=21
    Sheets("Homepage").Select
    Range("J2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=Investing!R[268]C[-9]"
    Range("J3").Select
    Sheets("Investing").Select
    ActiveWindow.SmallScroll Down:=-12
    Range("A249:B260").Select
    Selection.Copy
    Sheets("Daily Strategies").Select
    Range("E5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Homepage").Select
    Range("J2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=Investing!R[269]C[-9]"
    Range("J3").Select
    Sheets("Investing").Select
    Range("A249:B260").Select
    Selection.Copy
    Range("D283").Select
    Sheets("Daily Strategies").Select
    Range("G5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Homepage").Select
    Range("J2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=Investing!R[270]C[-9]"
    Range("J3").Select
    Sheets("Investing").Select
    Range("A249:B260").Select
    Selection.Copy
    Sheets("Daily Strategies").Select
    Range("I5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Homepage").Select
    Range("J2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=Investing!R[271]C[-9]"
    Range("J3").Select
    Sheets("Investing").Select
    Range("A249:B260").Select
    Selection.Copy
    Sheets("Daily Strategies").Select
    Range("K5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Homepage").Select
    Range("J2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=Investing!R[272]C[-9]"
    Range("J3").Select
    Sheets("Investing").Select
    Range("A249:B260").Select
    Selection.Copy
    Sheets("Daily Strategies").Select
    Range("M5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

I

`This code isn't waiting for the cells that will be copied to calculate` - what is your [calculation mode](https://learn.microsoft.com/en-us/office/troubleshoot/excel/current-mode-of-calculation)? — GSerg, Jun 19 '20 at 18:38
See here: https://www.ozgrid.com/VBA/calculation-mode-status.htm — RetiredGeek, Jun 19 '20 at 18:41
The code is writing the formula `=Investing!A270` into cell `J2` in worksheet `Homepage`. Then it is copying the values from range `A249:B260` in sheet `Investing` to range `E5:F16` in sheet `Daily Strategies`. What are you expecting it to do? Where are the cells not being calculated? What do 'certain function' and 'whole sheet' mean? Try adding the rest of the code and some clarification to your post. There is an [edit](https://stackoverflow.com/posts/62476185/edit) button below your post. — VBasic2008, Jun 19 '20 at 19:06
@VBasic2008 I updated my series for you I don't want to include the whole code because it is all the same process just pulling in a different equation into J2 and copying then pasting what that equation calculates. — AND AND AND, Jun 19 '20 at 20:00
no, no, no... You must clean up your code after macro recorder. Read first [How to avoid select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and [How to copy without clipboard](https://stackoverflow.com/questions/3901045/vba-copy-paste-without-clipboard) — Rafał B., Jun 19 '20 at 20:16
I need the formula(s) in cells `A249:B260` of sheet `Investing` and/or an explanation of how the values are changing there, if it isn't obvious. If `268` is the first number, what is the last, or is it dynamic? — VBasic2008, Jun 19 '20 at 20:17
@VBasic2008 369 is the last cell that it is pulling from. There is a stock ticker in 268, 269, 270 etc. That stock is being put into J2 that another cell references to pull in old stock prices. The old stock prices then spit out buy/sell signals that are in A249:B260. Those buy/sell signals change for each stock ticker in 268, 269 etc. My code goes through and changes the stock tickers perfectly but goes to fast and doesn't allow excel to calculate the different buy/sell signals for each stock. Sorry it is a lot and very complex, please let me know if I can explain more — AND AND AND, Jun 19 '20 at 20:29

score 0 · Answer 1 · answered Jun 19 '20 at 22:50

A Workbook Calculation

Copy the code into a standard module (e.g. Module1).
Adjust the constants including the workbook.
If it doesn't work, experiment with the out-commented lines containing Calculate one by one.

The Code

Option Explicit

Sub insertVarious()

    'Application.CalculateFullRebuild

    Const hpgName As String = "Homepage"
    Const hpgCell As String = "J2"

    Const invName As String = "Investing"
    Const invAddr As String = "A249:B260"
    Const invAddr2 As String = "A270:A371"

    Const dstName As String = "Daily Strategies"
    Const dstFirst As String = "E5"

    Dim wb As Workbook: Set wb = ThisWorkbook

    Dim hpg As Range: Set hpg = wb.Worksheets(hpgName).Range(hpgCell)
    Dim inv As Range: Set inv = wb.Worksheets(invName).Range(invAddr)
    Dim inv2 As Range: Set inv2 = wb.Worksheets(invName).Range(invAddr2)
    Dim UB1 As Long: UB1 = inv.Rows.Count
    Dim UB2 As Long: UB2 = inv.Columns.Count
    Dim NoA As Long: NoA = inv2.Rows.Count

    Dim Daily As Variant: ReDim Daily(1 To UB1, 1 To NoA * UB2)
    Dim Curr As Variant, j As Long, k As Long, l As Long
    For j = 1 To NoA
        hpg.Value = inv2.Cells(j).Value
        'hpg.Parent.Calculate
        'inv.Parent.Calculate
        Curr = inv.Value
        GoSub writeDaily
    Next j

    wb.Worksheets(dstName).Range(dstFirst).Resize(UB1, NoA * UB2) = Daily

    MsgBox "Data transferred.", vbInformation, "Success"

    Exit Sub

writeDaily:
    For k = 1 To UB1
        For l = 1 To UB2
            Daily(k, (j - 1) * 2 + l) = Curr(k, l)
        Next l
    Next k
    Return

End Sub

I want to say thank you for trying this out, I really appreciate this, but it doesn't allow the A249:B260 to calculate even after I tried the out-commented lines. This code is unbelievable fast and great besides the fact it doesn't allow them to calculate. Would you happen to have any other ideas? I will do anything you need me to do to get this figured out. — AND AND AND, Jun 20 '20 at 04:20
It still just isn't calculating with each time J2 changes with the values in A270:A371. If there was a way that it would copy all of them after they were able to calculate that would be amazing — AND AND AND, Jun 20 '20 at 14:29

How do I get VBA to Copy a range of cells, WAIT FOR THE CELLS TO CALCULATE and paste in another range?

1 Answers1

A Workbook Calculation