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!

 

[mumps]

I believe the number is 3.

 

[Fluff]

How about

Dim ShtCount As Long
ShtCount = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 11
Workbooks.Add
Application.SheetsInNewWorkbook = ShtCount

This will set the number of sheets created back to whatever the user has set as a default.

 

[AlexB123]

How about

Dim ShtCount As Long
ShtCount = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 11
Workbooks.Add
Application.SheetsInNewWorkbook = ShtCount

I found this approach afterward, and it seemed to work great! I will implement in my code as soon as I ensure I've no other computers that now have eleven as the default... LOL

 

[Jon Peltier]

Here's a slightly different approach, which doesn't require you to change any settings, however much you think it might be temporary. Because most users hate when you mess with their settings.

The function below shows how to add a workbook, then make sure it has the right amount of sheets. If there are more than the number you want, it deletes the extras. If there are fewer, it adds some after the last sheet. The function returns a reference to the workbook you've added, so you can continue to operate on it.

The sub above it shows how to call the function.

Sub AddWorkbookWith__Sheets()
  Dim Wkbk As Workbook
  
  Set Wkbk = AddNSheetWorkbook(11)
  
End Sub

Function AddNSheetWorkbook(Nwksht As Long) As Workbook
  Dim wb As Workbook
  Dim iWksht As Long
  
  Set wb = Workbooks.Add
  
  If wb.Worksheets.Count > Nwksht Then
    Application.DisplayAlerts = False
    For iWksht = wb.Worksheets.Count To Nwksht + 1 Step -1
      wb.Worksheets(wb.Worksheets.Count).Delete
    Next
    Application.DisplayAlerts = True
  ElseIf wb.Worksheets.Count < Nwksht Then
    For iWksht = wb.Worksheets.Count + 1 To Nwksht
      wb.Worksheets.Add After:=wb.Worksheets(wb.Worksheets.Count)
    Next
  End If
End Function

 

[Rick Rothstein]

Here's a slightly different approach, which doesn't require you to change any settings, however much you think it might be temporary. Because most users hate when you mess with their settings.

The function below shows how to add a workbook, then make sure it has the right amount of sheets. If there are more than the number you want, it deletes the extras. If there are fewer, it adds some after the last sheet. The function returns a reference to the workbook you've added, so you can continue to operate on it.

The sub above it shows how to call the function.

Sub AddWorkbookWith__Sheets()
  Dim Wkbk As Workbook
  
  Set Wkbk = AddNSheetWorkbook(11)
  
End Sub

Function AddNSheetWorkbook(Nwksht As Long) As Workbook
  Dim wb As Workbook
  Dim iWksht As Long
  
  Set wb = Workbooks.Add
  
  If wb.Worksheets.Count > Nwksht Then
    Application.DisplayAlerts = False
    For iWksht = wb.Worksheets.Count To Nwksht + 1 Step -1
      wb.Worksheets(wb.Worksheets.Count).Delete
    Next
    Application.DisplayAlerts = True
  ElseIf wb.Worksheets.Count < Nwksht Then
    For iWksht = wb.Worksheets.Count + 1 To Nwksht
      wb.Worksheets.Add After:=wb.Worksheets(wb.Worksheets.Count)
    Next
  End If
End Function

@Jon,

You set AddNSheetWorkbook up as a function, but you never return the created workbook back to the calling code so the Wkbk variable is set nothing after the AddWorkbookWith__Sheets subroutine is executed. The fix for your code is simple, of course, but I would like to offer a non-looping alternative to your function which I believe does the same thing as your code does (plus it returns the newly added workbook to the calling code)...

[table="width: 500"]
[tr]
	[td]Function AddNSheetWorkbook(Nwksht As Long) As Workbook
  Application.DisplayAlerts = False
  Set AddNSheetWorkbook = Workbooks.Add
  Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=Nwksht - 1
  Worksheets(Evaluate("TRANSPOSE(ROW(" & Nwksht + 1 & ":" & Worksheets.Count & "))")).Delete
  Worksheets(1).Select
  Application.DisplayAlerts = True
End Function[/td]
[/tr]
[/table]

 

[RoryA]

If you use:

workbooks.Add xlWBATWorksheet

the new workbook will only have one sheet, regardless of the application settings, so you can just add n - 1 new ones.

 

[Rick Rothstein]

If you use:

workbooks.Add xlWBATWorksheet

the new workbook will only have one sheet, regardless of the application settings, so you can just add n - 1 new ones.

Well, that is interesting to know. We have now gone from Jon's original function with 12 active lines of code and two loops to what I thought was a pretty clever kludge to eliminate the loops and halve the number of active lines of code to this rather simple two-liner...

[table="width: 500"]
[tr]
	[td]Function AddNSheetWorkbook(Nwksht As Long) As Workbook
  Set AddNSheetWorkbook = Workbooks.Add(xlWBATWorksheet)
  Worksheets.Add After:=Worksheets(1), Count:=Nwksht - 1
End Function[/td]
[/tr]
[/table]

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.

 

[Jon Peltier]

Rick -

Bummer. The weakness of Mr Excel is that you only have about ten minutes in which to go back and fix your post.

I like your loop-less version, and it's probably quicker than the loop. Probably a bit more obscure than looping.

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

Rory -

Good catch. I'd forgotten that one.

And Rick again -

The naming of new workbooks, worksheets, and other objects (shapes on a worksheet) has been a pain for so long, I'm not sure anyone would notice.

 

[Rick Rothstein]

Rick -

Bummer. The weakness of Mr Excel is that you only have about ten minutes in which to go back and fix your post.

Yeah, don't you just hate that... you something you should modify 9 minutes and 50 seconds after you posted it... Mr. Excel lets you edit it to your hearts content, but it won't post it because 10 minutes have elapsed... very frustrating indeed.

I like your loop-less version, and it's probably quicker than the loop. Probably a bit more obscure than looping.

If I am not mistaken, almost everything I post tends to be a bet obscure.

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

No, I don't think so. If the workbook you were running the macro from had, say, 9 worksheets and the user wanted to create a workbook with, say, 3 worksheets in it... using your suggestion would set the Count option to a negative number which would, in turn, raise an error.

The naming of new workbooks, worksheets, and other objects (shapes on a worksheet) has been a pain for so long, I'm not sure anyone would notice.

I noticed it. Of course, I have tendencies toward being obsessive-compulsive, so things like that tend to bother me more than it would bother "normal" people.