CODEWITHSUNDEEP
vbaexcelwait
De De De De
De De De De

Reputation: 426

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

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!

Upvotes: 1

Views: 1516

Answers (1)

Excel Developers
Excel Developers

Reputation: 2825

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

Upvotes: 1

Related Questions