Workbooks.Add with only 1 sheet in VBA

[maatman]

Hi,

I have the following code which creates a new workbook:

Workbooks.Add

The new workbook automatically contains 3 sheets, but I only want the workbook to have 1 sheet. I don't want to delete the other 2 sheets. I want that the new workbook just has 1 sheet.

Is this possible?

Regards

 

[VoG]

Try

Workbooks.Add (xlWBATWorksheet)

 

[Norie]

Use Template:=xlWBATWorksheet.

 

[outlawspeeder]

Application.SheetsInNewWorkbook = 1
Workbooks.Add

 

[Norie]

That's a bad idea - do you really want every new workbook you create to have 1 worksheet.

 

[Tom Urtis]

Hi,

I have the following code which creates a new workbook:

Workbooks.Add

The new workbook automatically contains 3 sheets, but I only want the workbook to have 1 sheet. I don't want to delete the other 2 sheets. I want that the new workbook just has 1 sheet.

Is this possible?

Regards

Simply:

Workbooks.Add 1

 

[aminex]

Simply:

Workbooks.Add 1

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">Application.Workbooks.Add(1)</code></pre>

 

[RoryA]

Unless you are returning the Workbook to a variable (or using Call), there shouldn't be parentheses:

Application.Workbooks.Add 1

Either way, I'm not sure it was worth resurrecting a 4 year old thread for...

 

[TheAverageBear]

My "number of sheets in a new workbook" is set to 1, because I would rather insert a new worksheet when needed, than delete out two extra ones all the time, when I usually only want one. I then tried the "Workbooks.Add 1" code, with two sheets, and the "2" option created a chart sheet. So, rather than permanently messing with the setting, you can hold and then restore the value, while still creating a new workbook with as many sheets in it as you desire.

This example creates a new workbook with two sheets:

 Dim ShtsCnt As Integer
    With Application
      ShtsCnt = .SheetsInNewWorkbook
      .SheetsInNewWorkbook = 2
      Workbooks.Add
      .SheetsInNewWorkbook = ShtsCnt
    End With

 

[mathematician]

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">Application.Workbooks.Add(1)</code>

Thanks. It helped me creating a new workbook and assigning it to an object variable using SET keyword (i.e 2 in 1).