-2

I want to add a new sheet in a Macro Enabled Excel File using VBA.

It doesn't matter the method that I use:

  • Sheets.add
  • Copying and pasting a sheet to create a new one

The new sheet is created (or copied) but the macro stops immediately, without any message error, and the rest of the code is not executed.

I converted the file to XLSX and when I run the code, the sheet is created and the code execution continues perfectly.

That's why I think that it is related to the Macro Enabled Excel type of file.

UPDATE:

I tested the code in another PC and Excel installation and the new sheet is created and the code goes on.

The only difference between the two computers is the Excel 2016 version:
Standard in one and Professional in the other.

Community
  • 1
  • 1
aacc
  • 1
  • 4
  • You need to show the relevant code. As it is, your question seems like a request for psychic debugging. Personally, I tend to do better debugging code when I can actually see the code. – John Coleman Jun 16 '16 at 11:47
  • Do you have any event code in the workbook that you are adding the sheet to? Or any conditional formatting that used UDFs? – Rory Jun 16 '16 at 12:18
  • let me telethink... ah.. there is one `End` in between two lines, you see that? :p – Rosetta Jun 16 '16 at 12:41
  • Thanks for your fast answer. Ha!, no, no, there isn't an "end" between lines. I've updated my question to confirm that it doesn't seem a code problem but an installation or something related with one of my equipments. – aacc Jun 16 '16 at 17:42

2 Answers2

0

Try this code in your xlsm file:

Dim ws As Worksheet
Set ws = Sheets.Add(After:=Sheets(Sheets.Count))

And let me know if this doesn't work.

lonestorm
  • 380
  • 1
  • 7
  • I've updated my question. Please have a look. Only to confirm you, I tried: Sheets.Add or sheetSource.Copy After:=Sheets(Sheets.Count) to copy a sheet And in these two cases, the macro stops after creating/copying the new sheet. But, as I've commented in my update, it seems that it's only happening in my office PC So, this is something that I have to investigate. Thanks – aacc Jun 16 '16 at 17:51
  • Does you workbook use the ribbon? Just a thought, but it could be that adding and copying a sheet triggers some other process that your code does not step through and that could be where the failure occurs. – lonestorm Jun 17 '16 at 08:29
  • Yes, it uses the ribbon. May be I'm wrong, but I think that it can be a Bug. I've seen that there is a difference in the 2016 Excel version: In the 16.0.4266.1001, the execution stops after Adding the new Sheet And, in the 16.0.6925.1016, the execution continues perfectly after adding it. – aacc Jun 20 '16 at 10:56
  • It could definitely be a bug. There have been recognised bugs in several versions of Excel that present when adding and removing worksheets. None of these, to my knowledge, quite match your description, bu that doesn't mean to say the bug doesn't exist. However, I have run into issues trying to debug my programs when working with the ribbon in the past, so I wouldn't rule that out. It will be tricky for you to isolate the problem, so good luck! – lonestorm Jun 20 '16 at 11:08
  • I got a COMException on this on the Add() call if it is a XLSM file. – David Thielen Jun 03 '18 at 13:10
0

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.Add or Worksheets.Add line. I found that this was only happening where I was attempting to add a sheet to a workbook that contained VBA code. 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: Application.AutomationSecurity = msoAutomationSecurityLow

M1chael
  • 251
  • 1
  • 9