Copy Paste Formulas - Any Speed Tips?

Hi guys,

I have the following code that works well just after opening the file but gets slower and slower as the loops progress. I'm copying and pasting formulas from 'FormulaRange' to 'DestinationRange' (variable). I feel like I'm not cleaning up something, which is slowing it down. I'd like to keep it running at optimum speed the entire process. Any tips?

Sub CopyPasteDown()

Application.ScreenUpdating = False

Dim r As Integer
For r = Range("J1") To Range("L1")

Dim FormulaRange As Range
Set FormulaRange = Range("A4:YY4")
Dim DestinationRange As Range
Set DestinationRange = Range(Cells(r, 1), Cells(r, 674))
Dim FirstCell As Range
Set FirstCell = Cells(r, 1)

FormulaRange.Select
Selection.Copy
FirstCell.Select
ActiveSheet.Paste

'FormulaRange.Copy DestinationRange - not working - I have a UDF that doesn't calculate when I use this so used the long way

DestinationRange.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Next r

Application.ScreenUpdating = True

End Sub

Cheers, Rob

Not sure if this is what you're after or whether it's much faster. Test on a copy first.

Sub killerkoz17()
Dim r As Integer
Dim FormulaRange As Range
Dim DestinationRange As Range
Application.ScreenUpdating = False
Set FormulaRange = Range("A4:YY4")
For r = Range("J1") To Range("L1")
Set DestinationRange = Range(Cells(r, 1), Cells(r, 674))
DestinationRange.Value = FormulaRange.Value
Next r
Application.ScreenUpdating = True
End Sub

Thanks for the response John. I'm actually trying to copy and paste formulas, rather than values, so this didn't work (just pasted the values in every row). It's a reasonably simple macro. I guess there are three things I'm trying to improve:

1) avoid "selecting"
2) store the formulas in the 'FormulaRange' as a single variable (if possible, to avoid copying them every loop)
3) clean up each loop to avoid storing information and clogging up speed.

All ideas appreciated. I'm using this macro a lot!

Hey Izandol,

UDF is from the web. It's basically a LOOKUP function that finds the nth occurrence of a string in a range and returns a value relative to the nth occurrence. The UDF works fine in the worksheet. It evens works well when I do the "long" copy and paste method. But when I do the "quick" copy and paste method (i.e. FormulaRange.Copy DestinationRange) it does not calculate and returns #VALUE! (that is the paste special value). Tried DesinationRange.Calculate and I added Application.Volatile myself but they had no effect.

Function NTHOCCURENCE(range_look As Range, find_it As String, _
occurrence As Long, offset_row As Long, offset_col As Long)

Application.Volatile

Dim lCount As Long
Dim rFound As Range

Set rFound = range_look.Cells(1, 1)

    For lCount = 1 To occurrence

        Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)

    Next lCount

NTHOCCURENCE = rFound.Offset(offset_row, offset_col)

End Function

Any clues on getting this working with the quick copy and paste method would be great!

Cheers, Rob

Hey Izandol,

Thanks for the reply. Unfortunately, it just copied and pasted the values (not the formulas) from 'FormulaRange' to 'DestinationRange'. I was having a look through the 'Range' object properties and methods and can't see anything that stores multiple formulas, just a single formula. But the formulas must be stored somewhere, maybe the clipboard? I'll investigate that option and reply soon.

Cheers, Rob

Rob:

Try changing this:

        With DestinationRange
            .Formula = FormulaRange.Formula
            .Value = .Value
        End With

To this:

DestinationRange.Formula = FormulaRange.Formula

See if that does what you want?

Originally Posted by Izandol

This is not correct - what was testing you did?

Just playing around with my worksheet and the various suggestions in this post. It's not a difficult macro but we never got it working. Seems to me that's not what this method (Range.Formula) is designed for. As soon as I did a single formula it worked fine. Do you have a working example with multiple formulas? I'm happy to play with that and edit.

Originally Posted by Izandol

Put different formulas in A1:A3 then run

Sub formulaFu()
Range("b1:b3").formula = range("a1:a3").formula
End sub

No. This does not work.

No prob. Here we go.

Originally Posted by Izandol

You put formulas in A1:C1, not A1:A3. You must move formulas or change code to:

Sub FormulaFu()
Range("A2:C2").Formula = Range("A1:C1").Formula
End Sub

Sorry, woops! Ok, that's interesting. So, yes, you're right, the formulas do transfer (I'll edit that post above in a minute). The issue is that the formulas are not relative. It literally pastes the formulas, as if they are strings. It looks like the copy and paste method I have above is as good as it will get.

Thanks for the help,

Cheers, Rob

Yes! Range.FormulaR1C1 is exactly what I needed. I wish this was part of the conversation at the start! Thanks Izandol for your perseverance. A big learning curve for me, much appreciated. Here's the final code:

Sub CopyPasteDown()

Application.ScreenUpdating = False

Dim r As Integer
For r = Range("J1") To Range("L1")

Dim DestinationRange As Range
Set DestinationRange = Range(Cells(r, 1), Cells(r, 674))
Dim FormulaRange As Range
Set FormulaRange = Range("A4:YY4")
Dim FirstCell As Range
Set FirstCell = Cells(r, 1)

With DestinationRange
    .FormulaR1C1 = FormulaRange.FormulaR1C1
    .Value = DestinationRange.Value
End With

Next r

Application.ScreenUpdating = True

End Sub

It's not maintaining the original format unfortunately, even when I use .NumberFormat (sigh) but this code is as good as it gets.

Cheers, Rob