Excel Vba stops excution after deleting sheet

Question

I am new to VBA. I have written a code to delete a particular sheet. After execution of that delete sheet macro, excel macro stopped execution. It didnt execute futher..

Here is my code..

Sub CopyAcross()
Dim sheetName As String
sheetName = "Master_Base"
If WorksheetExists(sheetName) Then
 DeleteSheet (sheetName)
End If
MsgBox "Debug"

Workbooks("Master_Base.csv").Sheets("Master_Base").Copy Before:=Workbooks("Copy of    test.xlsm").Worksheets("Sheet3")
End Sub 

Sub DeleteSheet(strSheetName As String)
' deletes a sheet named strSheetName in the active workbook
Application.DisplayAlerts = False
Sheets(strSheetName).Delete
Application.DisplayAlerts = True
End Sub

Can any one help on this,

Thanks in advance.

Answer 1

I was experiencing the same issue, on a Windows 7 computer with Excel version 16.0.10730.20264 32-bit, the code ran fine without issue. However, on a Windows 10 computer with the same Excel install version, the macro would immediately stop execution following the Sheets.Delete line.

I found that this was only happening where I was attempting to manipulate a workbook that contained VBA code, that I had opened during the macro.

The issue is caused by the macro security settings on the computer. If you set Automation Security to Low before opening the workbook, you should no longer get the error:

Use the code:

Application.AutomationSecurity = msoAutomationSecurityLow