Wait for Formula calculation to finish before copying to next worksheet. Excel VBA

Question

I have a problem with copying columns from one sheets to another. In the source of the worksheet, some of the cells are integrated with formula which are only calculated upon opening the workbook. Because of it, the result I end up copying from one workbook to another are the initial value. I have tried some of the solution but It doesn't work properly.

My code:

Application.Calculation = xlCalculationAutomatic

Set wb = Application.Workbooks.Open(FilePath, ReadOnly:=True, UpdateLinks:=0)

    If Not Application.CalculationState = xlDone Then
        DoEvents
    End If

wb.Sheets("Unified").Columns("A").Copy activeWB.Sheets("report").Range("A1")

For more information, this is the formula of a cell from the source worksheet:

"=VLOOKUP(B649,'filesystem'!$A:$N,14,FALSE)"

And the result I gotten is #REF!

what happens when you set `Application.CalculationState` as automatic before you open the workbook? — Siddharth Rout, Nov 13 '14 at 07:22
@Siddharth Routh Can't compile: Can't assign to read-only property error. — De De De De, Nov 13 '14 at 07:25
@DeDeDeDe could you insert the copy thing inside if and omitting not in the `If Not`? — ZAT, Nov 13 '14 at 07:31
Sorry I meant `Application.Calculation = xlCalculationAutomatic` — Siddharth Rout, Nov 13 '14 at 07:35
@ZAT and Siddharth Rout Both of the result are the same. Is it because the Application does not refer to wb.Sheets("Unified") ? If I substitute the Application with wb.Sheets('unified"), it's not working anymore. *Edited: I'm taking about Application.CalculationState = xlDone — De De De De, Nov 13 '14 at 07:39
Please paste the code like I showed above before `Set wb = Application.Workbooks.Open...` And remove the `wb.Sheets("Unified").Calculate` and then try again — Siddharth Rout, Nov 13 '14 at 07:46
Also if you open the workbook manually, how much time does it take to finish the calculation? — Siddharth Rout, Nov 13 '14 at 07:47
@SiddharthRout About 7 seconds. I have edited the code above in my post and tested it. The result is still being taken at initial value. — De De De De, Nov 13 '14 at 07:52
Ok. Please try the code that I posted below. You may have to refresh the page — Siddharth Rout, Nov 13 '14 at 07:56
@DeDeDeDe you want to copy with formula or only value. And you stated earlier that problem was calculation was not getting completed before copy. — ZAT, Nov 13 '14 at 08:12
@ZAT essentially, I just need the result. I don't need the formula. — De De De De, Nov 13 '14 at 08:16
Ok try this... Open the file `filesystem` as readonly before you open the source file. That will speedup the calculations. — Siddharth Rout, Nov 13 '14 at 08:17
@SiddharthRout I have no idea if it's eligible for me to show the workbook. But your help is appreciated. I'll try my best. — De De De De, Nov 13 '14 at 08:19

score 1 · Accepted Answer · answered Nov 13 '14 at 17:40

You are copying the formula, not the result.Try

Set SourceRange = Intersect(wb.Sheets("Unified").Columns("A"), wb.Sheets("Unified").UsedRange)
Set TargetRange = activeWB.Sheets("report").Range("A1").Resize(SourceRange.Rows.Count,1)
TargetRange.Value = SourceRange.Value

Wait for Formula calculation to finish before copying to next worksheet. Excel VBA

1 Answers1