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

Tmdean · Accepted Answer · 2014-01-03T23:28:56.527

3

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

edited Jan 03 '14 at 23:28

answered Jan 03 '14 at 23:17

Tmdean

9,108
43
51

Brilliant! Of course I should have thought about the non-clipboard way, and as a bonus, it should also run faster. Thanks!! – user3158939 Jan 03 '14 at 23:37
+1 yep, always best to work directly where possible as you suggested, – brettdj Jan 04 '14 at 09:20

Copy PasteSpecial in For Each loop quits irregularly

1 Answers1