Paste method of worksheet class failed

[AlexScorpene]

Hi,
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?

I posted the same question on stackoverflow and one of the suggestions was to modify code to following but that did not work popping message Range class failed

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

Both of the above codes work for small data. Like if iCustomer is around 120000, it works like a charm. Is it possible that Excel is not able to use available RAM or something?

Thank you

 

[Joe4]

I posted the same question on stackoverflow

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

So please provide that link.

 

[AlexScorpene]

Sorry about that. Here is the link vba - Paste method of worksheet class failed - Stack Overflow