Insert formulas (range) then copy>paste values only

Question

I want to make a macro that copies formulas into the range Q6:Q2500. After the macro inserted the formulas it should copy>paste values only.

[...]
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Range("Q6").Formula = "=IF(INDIRECT(""A""&ROW())="""","""",CONCATENATE(INDIRECT(""A""&ROW()),""/"",INDIRECT(""B""&ROW()),""/"",INDIRECT(""E""&ROW()),""/"",INDIRECT(""P""&ROW())))"
Range("Q6").Copy
Range("Q6:Q2500").PasteSpecial (xlPasteAll)
Range("Q6:Q2500").PasteSpecial xlPasteValues

End Sub

When I run this macro it inserts the formulas as intended. But when I try to paste values only I get the first value repeated till Q2500. I think it's because the formula update is to slow.

I saw another thread on Stack but the answer was to convert the formula into a VBA function. I don't know how to convert this formula.

Answer 1

You are converting before calculation is completed which is causing the problem.

However you are first placing the Formula to the Cell and then copying the Value into the Cell. This can be shortened to letting VBA calculate the Value and place it into the Cell. Its advised to not make use of .Copy and .Paste if it can be avoided.

See example:

Sub JoinStrings()
    Dim cell As Range
    Dim strJoin As String

    With Worksheets("Sheet1")
        For Each cell In .Range("Q6:Q2500")
            If .Range(cell.Offset(0, -16).Value) <> "" Then
                strJoin = .Range(cell.Offset(0, -16).Value).Value & "/"
                strJoin = strJoin & .Range(cell.Offset(0, -15).Value).Value & "/"
                strJoin = strJoin & .Range(cell.Offset(0, -12).Value).Value & "/"
                strJoin = strJoin & .Range(cell.Offset(0, -1).Value).Value
                cell.Value = strJoin
            End If
        Next
    End With
End Sub