CODEWITHSUNDEEP
vbaexcel
blue piranha
blue piranha

Reputation: 3874

Paste method of worksheet class failed

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?

Upvotes: 0

Views: 2376

Answers (1)

cyboashu
cyboashu

Reputation: 10433

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.

Upvotes: 1

Related Questions