CODEWITHSUNDEEP
excelfilecopy-pastevba
Jack Armstrong
Jack Armstrong

Reputation: 1249

VBA crashes when run, but works when stepped through

Answered, see below.

I have a block of code that open a file, copies date from open file, then pastes to a different file. When I step through the code, everything works. When I run through the code, it reboots excel. Thoughts?

Sub copyPastefile()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim currwb, wb1, wb2, wb3, wb4, wb5 As Workbook
Set currwb= ThisWorkbook
Set wb1 = Workbooks.Open(Filename:="FileLocation\workbook1.xls")

  wb1.Sheets("Sheet1").Range("C9:c11").Copy
  ThisWorkbook.Sheets("wb1").Range("k18").PasteSpecial Paste:=xlPasteValues
  wb1.Sheets("Sheet1").Range("o22:w22").Copy
  ThisWorkbook.Sheets("wb1").Range("e35").PasteSpecial Paste:=xlPasteValues
  wb1.Sheets("Sheet1").Range("b38:I48").Copy
  ThisWorkbook.Sheets("wb1").Range("a5").PasteSpecial Paste:=xlPasteValues
  wb1.Close
  currwb.Sheets("wb1").Range("A1").Activate

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

ThisWorkbook.Activate
ThisWorkbook.Sheets("Sheet6").Activate
Range("A1").Activate
currwb.Save
MsgBox ("Done")

This code then repeats for wb2, wb3, etc, but now pastes into its respective sheet.

Upvotes: 0

Views: 5344

Answers (1)

matt2103
matt2103

Reputation: 321

I disagree with the above posters--this does not sound like a ghost break. A ghost break sends you to debug for no reason (ala using the pause/break key), while you're experiencing a full crash of Excel.

My theory: The copy/paste function in excel is a highly intensive process. This is okay for isolated copy/pastes. In the case of your code, especially without any Application.CutCopyMode = False statements (not all seemingly extraneous statements inserted by the record function are actually extraneous), you're stacking a bunch of copies without ever clearing them. The application then runs out of memory and crashes.

My solution:

Replace each of these lines-

workbook1.Sheets("Sheet1").Range("C9:c11").Copy
ThisWorkbook.Sheets("wb1").Range("k18").PasteSpecial Paste:=xlPasteValues

With something like this-

ThisWorkbook.Sheets("wb1").Range("k18:k20").value = workbook1.Sheets("Sheet1").Range("C9:c11").value

Your code will stop crashing, and also run faster.

Upvotes: 4

Related Questions