0

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.

Community
  • 1
  • 1
Jack Armstrong
  • 1,182
  • 4
  • 26
  • 59
  • 1
    Do you reset Applicaiton.ScreenUpdating to True and .Calculation to Automatic at the end of your script? Is there more code missing here? When in debug mode, the Application.ScreenUpdating command is ignored and left at True, so that would likely explain why it "works" in debug step-through mode. I would wager it is doing what you expect in run-time, you just don't see the updated results. – shmicah Apr 03 '18 at 14:59
  • This sounds like a ghost break - https://stackoverflow.com/questions/2154699/excel-vba-app-stops-spontaneously-with-message-code-execution-has-been-halted – Vityata Apr 03 '18 at 15:05
  • 2
    All the wb variables are not considered workbooks, they are considered variants. The only one considered a workbook is wb5. You need to Identify each one as a workbook. `dim currwb as workbook, wb1 as workbook, wb2 as workbook`...etc. – Davesexcel Apr 03 '18 at 15:09
  • 1
    You also have `set wb1=` but then call it `workbook1.sheets(` later in the code – Davesexcel Apr 03 '18 at 15:11
  • That's what I get when I try to adjust my code. Fixed the syntax error and I had the .Calc Automatic at the end of my script already. Apologies for that. – Jack Armstrong Apr 03 '18 at 16:01

1 Answers1

4

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.

matt2103
  • 311
  • 3
  • 13
  • That's not why it's crashing, the workbook variables are incorrect. The OP is making wb1 and workbook1 the same, but only wb1 is set – Davesexcel Apr 03 '18 at 15:20
  • 1
    @Davesexcel: If OP is to be believed, the code works in debug but not normal. If it was syntax error wouldn't it still fail in step-through? I think OP is leaving a lot of information out. – shmicah Apr 03 '18 at 15:57
  • 2
    @Davesexcel I'm not sure if disagreeing with my solution is an appropriate reason for a downvote. Please reconsider. I provided a solution based on all the information given to me, rather than ignoring the majority of the post and focusing only on the code. I also appropriately posted my solution as a solution, not as a comment. – matt2103 Apr 03 '18 at 16:18
  • Thank you for the above. I probably also should have said it was working earlier and the crash is brand new. I fixed it by "dimming" each variable as a workbook, but since I have no way to accept it as an answer. This one will have to do.... – Jack Armstrong Apr 03 '18 at 18:50
  • No problem, @JackArmstrong ! And thanks for accepting this answer--just to make sure though, your problem got solved, right? – matt2103 Apr 03 '18 at 20:58
  • Ran it again today and it crapped out. Used your copy paste trick and it worked. Could have been a bit both. – Jack Armstrong Apr 04 '18 at 13:39