What are the default number of sheets created by Workbooks.Add???

[AlexB123]

Hi all,

I was re-writing an old macro, and I needed to create a workbook with 11 sheets. So I set Application.SheetsInNewWorkbook = 11.

This worked, but I noticed every computer that macro runs on now creates new workbooks with 11 sheets ...

I need to figure out the correct number of sheets to set the property back correctly, before I wreck havoc on the many macros that call Workbooks.Add.

Any help is appreciated!

 

[Jon Peltier]

Shouldn't your code say Count:=Nwksht - Worksheets.Count instead of - 1?

Oh, I get it. You're adding the worksheets, and maybe adding too many, then removing the excess in the next step. I didn't read your whole procedure very closely.

 

[Jon Peltier]

The only downside to using the xlWBATWorksheet constant, but it is just a minor one, is that it names the new workbooks as Sheet1, Sheet2, etc. as you add successive new workbooks which is kind of unconventional for workbook names.

What bothers me is the way the sheet names (codenames) in the VB project get out of sync with the sheet names in Excel.

Start with Sheet1 in Excel. The sheet's codename is also Sheet1.

Add two sheets in Excel, Sheet2 and Sheet3. Their codenames are Sheet2 and Sheet3.

Delete Sheet2 in Excel, and Sheet2 is also gone from the project.

Add another sheet in Excel. It's Sheet4, but its codename is Sheet2.