Copy PasteSpecial in For Each loop quits irregularly

Question

I have a routine to copy hidden backup formulas for a selection that has worked fine in Excel 2003/2007/2010. When I run it in 2013, it starts to work through the selection and then just quits, no error message is given. When testing just this loop, it might run for 2 cells, it might run for 5 cells, but never more than 6. Because the selections are mostly groups of non-contiguous cells, I can't easily copy the entire range at one time without major surgery to rename all the ranges.

Any idea what happened to 2013? What can I do to make it work now?

Here is the core of the code that is not running:

Sub TestLoop()
    Dim c As Range

    For Each c In Selection
       If c.Locked = False And Not IsNumeric(c.Offset(0, 30).Formula) _
           And c.Offset(0, 30).Formula <> "" Then
          With c
             .Offset(0, 30).Copy
             .PasteSpecial xlPasteFormulas
          End With
       End If
   Next c

End Sub

Answer 1

Try skipping the clipboard and just copying the formula directly. I run into issues with macros that use the clipboard like this - I think since the clipboard is really intended for the end user, it just isn't designed to be used by programs as a temporary data store, so you'll encounter some flaky behavior.

Change this code:

.Offset(0, 30).Copy
.PasteSpecial xlPasteFormulas

To this:

.FormulaR1C1 = .Offset(0, 30).FormulaR1C1