Paste method of worksheet class failed

Question

Please consider the following Excel VBA code

 Private Sub Copy_Formula(Dest As Integer, iCustomer As Long)
  Sheets("INPUT").Select
  Cells(2, Dest).Select
  Selection.Copy
  Range(Cells(4, Dest), Cells(3 + iCustomer, Dest)).Select
  ActiveSheet.Paste '<------ Errors out
  Application.CutCopyMode = False
  Calculate
  Range(Cells(4, Dest), Cells(3 + iCustomer, Dest)).Select
  Selection.Copy
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  Application.CutCopyMode = False
 End Sub

When this sub is called, value of Dest is 84 and iCustomer is 1044770.

I was just wondering if there is a solution to solve this paste operation? Is it possible to increase the buffer size somewhere to accommodate this massive data? We are using 32 bit Excel 2010 on a 32 GB RAM machine with a 64 bit OS.

Would a 64 bit Excel work?

Answer 1

Always better to use fully qualified names instead of ActiveSheet.

Try this

 Private Sub Copy_Formula(Dest As Integer, iCustomer As Long)
  Sheets("INPUT").Cells(2, Dest).Copy Sheets("INPUT").Range(Cells(4, Dest), Cells(3 + iCustomer, Dest))
  Application.CutCopyMode = False
  Calculate

  Sheets("INPUT").Range(Cells(4, Dest), Cells(3 + iCustomer, Dest)).Copy

  Sheets("INPUT").Range(Cells(4, Dest), Cells(3 + iCustomer, Dest)).Cells(1, 1).PasteSpecial _
                            Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  Application.CutCopyMode = False
 End Sub

Also 32 or 64 bit, its all the same for this code.