Any way to reduce Copy > Paste Formulas > Copy > Paste Values from 4 processes to 2?

[d0rian]

Silly Q, but a pattern that I use a lot in my file is copying a formula from a 'source' cell (e.g. A1) to a defined range (e.g. A2:A99), but since I want values A2:A99 to be hardcoded for file speed, I'll then Copy A2:A99 and paste_values A2:A99.

So the code ends up looking something like this:

Range("formula").[B]Copy[/B]
Range(Range("paste_range").Value).[B]PasteSpecial[/B] Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Range(Range("paste_range").Value).[B]Copy[/B]
Range(Range("paste_range").Value).[B]PasteSpecial [/B]Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False

Is there a faster way to do that? Like some kind of paste function that in a single action tells Excel to paste the RESULT of the formulas rather than the formulas themselves?

 

[Fazza]

Please try below. I think you'll need two steps whatever approach you use.

With Range(Range("paste_range").Value)
    .FormulaR1C1 = Range("formula").FormulaR1C1
    .Value = .Value
End With

 

[d0rian]

Thanks, will try. Sounds like you're saying, though, that you wouldn't expect this method to be any faster than my original one, is that right?

 

[Fazza]

No. I'm saying you can't get away from the the two steps - formulas and then values. There won't be a one step paste function that does what you want in a single action.

Regarding speed, what I posted will (untested) be faster. How much I can't even guess. if you set up a loop & time doing hundreds of thousands of each approach it'd be interesting to learn.

regards

PS. I'm assuming the formula itself is fast. If it were some really slow array formula then the speed gain from the suggestion might seem less dramatic - with the bulk of the time being consumed by a slow formula calculation rather than the code execution. If that is the case, change the slow formula approach.