Excel VBA crashes after subroutine finishes

This script is to reset a template, by copying a hidden worksheet template and deleting the existing sheet (after repopulating some reference data). I have tested it and it runs fine in debugging mode.

Option Explicit
Sub reset_PrintLayout_byCopy()
   'the script replace the used printlayout with a copy from the hidden master.

   Dim MeetingData() As String
   Dim i As Integer
   Dim j As Integer
   Dim currentSheet As String
   Dim datacolumns() As String
   Dim userConfirm As String
   ReDim Preserve MeetingData(3, 2)
   ReDim Preserve datacolumns(2)

   'warning about deleting data
   userConfirm = MsgBox(Prompt:="Resetting the template will erase all data on the " _
   & "PrintLayout Template. Choose ""Cancel"",  if you wish to save the file first", _
   Buttons:=vbOKCancel, Title:="Data to be erased!")

  If (userConfirm = vbCancel) Then
      Exit Sub
  End If

  'set parameters
  datacolumns(0) = "D1"
  datacolumns(1) = "I1"


  'stop screen updating and displaying warnings
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False


  'set active sheet
  currentSheet = ActiveSheet.Name

  'capture meeting data already filled out
  For j = 0 To UBound(datacolumns) - 1
      For i = 1 To 3
          If Worksheets(currentSheet).Cells(i, Range(datacolumns(j)).Column).Value <> ""    Then
             开发者_如何学编程 MeetingData(i - 1, j) = Worksheets(currentSheet).Cells(i,  Range(datacolumns(j)).Column).Value
           End If

       Next i
   Next j

   'make hidden template visible
   Worksheets("hiddenPrintLayoutTemplate").Visible = True

  'Rename current Sheet
       Sheets(currentSheet).Name = "used_Print_Layout"

  ''add a new sheet
  '    ActiveWorkbook.Worksheets.Add(before:=Sheets("used_Print_Layout")).Name = "PrintLayout Template"

   'copy hiddentemplate before current sheet
       Worksheets("hiddenPrintLayoutTemplate").Copy before:=Sheets("used_Print_Layout")
       ActiveSheet.Name = currentSheet

   'set rowheight for title rows
       Range("A12").EntireRow.RowHeight = 24
       Range("A18").EntireRow.RowHeight = 24

   'delete current used printlayout
        Worksheets("used_Print_Layout").Delete

   'refilled meeting data
   For j = 0 To UBound(datacolumns) - 1
       For i = 1 To 3
           If MeetingData(i - 1, j) <> "" Then
               Worksheets(currentSheet).Cells(i, Range(datacolumns(j)).Column).Value =  MeetingData(i - 1, j)
            End If
       Next i
   Next j

   'hide PrintLayout template
   'Worksheets("hiddenPrintLayoutTemplate").Visible = xlSheetVeryHidden
   'Sheets("PrintLayout Template").Select

   'activate screenupdating and display warnings
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True
End Sub 

When run it in macro mode on button, it runs, but excel crashes, when it is done. I cannot find what the issue is. Any ideas?

---

I am not sure if by debugging you mean stepping through line by line, but you could try inserting stop statements at key points in the code. So for example, you could put a stop statement in the following part:

'capture meeting data already filled out
For j = 0 To UBound(datacolumns) - 1
    For i = 1 To 3
        If Worksheets(currentSheet).Cells(i, Range(datacolumns(j)).Column).Value <> "" Then
            MeetingData(i - 1, j) = Worksheets(currentSheet).Cells(i,Range(datacolumns(j)).Column).Value
        End If
    Next i
Next j

stop

'make hidden template visible
Worksheets("hiddenPrintLayoutTemplate").Visible = True

You could see if the code runs fine up until that point (i.e. run it without debugging). If it does, remove the stop statement and place it further down the code. Repeat this until you find the statements which cause your crash - perhaps the reason would appear then.

---

In general, if you get an odd crash in Excel VBA, try switching the Windows default printer to Microsoft XPS Document Writer. Seems odd, but that has worked for me on problems where I've wasted many hours only to find this is the culprit.