Excel crashes when launching macro

Nick Suy 1 Reputation point
2020-06-15T19:27:06.553+00:00

Hi,

Excel keeps on crashing when running this macro.
For some context.

  • the user pushes a button VERZENDEN en makes it run.
  • There are multiple small other buttons that just delete a field
  • For every small 'reset' button, there is a sellection option of 5 buttons

What does the main button do?

  • copy certain info to another sheet
  • make sure the next time the button is pressed a new collumn is started on the left of the previous one.(by adding a collumn)
  • delete certain fields to reset the input sheet completely
  • There are simple scores that are copied, just as dates and comments (comments go from 5 cells to 1)

Any idea why it keeps crashing?

Here's the code (+ de code of 1 of these buttons)

Sub VERZENDEN()

'Controle verzenden
Dim Antwoord As VbMsgBoxResult
answer = MsgBox("Ben je zeker?", vbYesNo + vbGuestion + vbDefaultButton1, "VERZENDEN")
If answer = vbYes Then

'Voeg kolom toe
Worksheets("Resultaten").Columns("B:Z").Copy Worksheets("Resultaten").Columns("C")
Worksheets("Resultaten").Columns("B").ClearContents


'kopieer en plak waardes

'Deel 1
Worksheets("Mentor").Range("J3:J8").Value = Worksheets("Resultaten").Range("B3").Value

Worksheets("Mentor").Range("C9:G9").Copy
Worksheets("Resultaten").Range("B9").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

Worksheets("Mentor").Range("O9").Copy
Worksheets("Resultaten").Range("B2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

'Deel 2
Worksheets("Mentor").Range("J12:J23").Value = Worksheets("Resultaten").Range("B12").Value

Worksheets("Mentor").Range("C24:G24").Copy
Worksheets("Resultaten").Range("B24").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

Worksheets("Mentor").Range("O24").Copy
Worksheets("Resultaten").Range("B11").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

'Deel 3
Worksheets("Mentor").Range("J27:J29").Value = Worksheets("Resultaten").Range("B27").Value

Worksheets("Mentor").Range("C31:G31").Copy
Worksheets("Resultaten").Range("B31").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

Worksheets("Mentor").Range("O31").Copy
Worksheets("Resultaten").Range("B26").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

'Deel 4
Worksheets("Mentor").Range("J34:J35").Value = Worksheets("Resultaten").Range("B34").Value

Worksheets("Mentor").Range("C36:G36").Copy
Worksheets("Resultaten").Range("B36").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

Worksheets("Mentor").Range("O36").Copy
Worksheets("Resultaten").Range("B33").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

'Deel 5
Worksheets("Mentor").Range("J39:J40").Value = Worksheets("Resultaten").Range("B39").Value

Worksheets("Mentor").Range("C41:G41").Copy
Worksheets("Resultaten").Range("B41").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

Worksheets("Mentor").Range("O41").Copy
Worksheets("Resultaten").Range("B38").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

'Deel 6
Worksheets("Mentor").Range("J44:J45").Value = Worksheets("Resultaten").Range("B44").Value

Worksheets("Mentor").Range("C46:G46").Copy
Worksheets("Resultaten").Range("B46").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

Worksheets("Mentor").Range("O46").Copy
Worksheets("Resultaten").Range("B43").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

'Deel 7
Worksheets("Mentor").Range("J49:J50").Value = Worksheets("Resultaten").Range("B49").Value

Worksheets("Mentor").Range("C51:G51").Copy
Worksheets("Resultaten").Range("B51").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

Worksheets("Mentor").Range("O51").Copy
Worksheets("Resultaten").Range("B48").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

'Deel 8
Worksheets("Mentor").Range("J54:J56").Value = Worksheets("Resultaten").Range("B54").Value

Worksheets("Mentor").Range("C57:G57").Copy
Worksheets("Resultaten").Range("B57").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

Worksheets("Mentor").Range("O57").Copy
Worksheets("Resultaten").Range("B53").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

'Deel 9
Worksheets("Mentor").Range("J60:J63").Value = Worksheets("Resultaten").Range("B60").Value

Worksheets("Mentor").Range("C64:G64").Copy
Worksheets("Resultaten").Range("B64").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

Worksheets("Mentor").Range("O64").Copy
Worksheets("Resultaten").Range("B59").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False


'Kopieer en plak datum
Worksheets("Mentor").Range("G1").Copy
Worksheets("Resultaten").Range("B1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

'Leeg cellen
Sheets("Mentor").Select
Range("J3:J8,C9:G9,J12:J23,C24:G24,J27:J30,C31:G31,J34:J35,C36:G36,J39:J40,C41:G41,J44:J45,C46:G46,J49:J50,C51:G51,J54:J56,C57:G57,J60:J63,C64:G64").Select
Application.CutCopyMode = False
Selection.ClearContents

'Box bevestiging verzenden
VBA.Interaction.MsgBox "Gegevens verzonden", , "VERZENDEN"

Else
    Exit Sub
End If

End Sub

Sub Reset1_1()
Range("J3").Select
Selection.ClearContents
End Sub

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
35,985 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Dave Patrick 426.1K Reputation points MVP
    2020-06-15T20:20:27.563+00:00

    Excel is not currently supported here on QnA. They're actively answering question in dedicated forums here.

    https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel

    https://social.technet.microsoft.com/Forums/office/en-US/home?forum=excel

    --please don't forget to Accept as answer if the reply is helpful--

    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows Server] Datacenter Management

    Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.

    0 comments