-1

I have searched everywhere for an answer to this, but I can't find one. how do I check if there is more than 1 worksheet in Workbook.

0m3r
  • 12,286
  • 15
  • 35
  • 71
Robby
  • 843
  • 3
  • 19
  • 53
  • 3
    Try this: `If ThisWorkbook.Worksheets.Count > 1 Then MsgBox "There is more than one sheet in this Excel file."` – Ralph Oct 13 '16 at 13:29
  • 1
    Well you must not have looked *everywhere*... :) Bookmark [this](https://msdn.microsoft.com/en-us/library/office/ff194068(v=office.14).aspx) and refer back to it often. It takes a little bit to get used to the way the Object Model is organized, and you'll do a fair amount of digging & poking about various rabbit holes, but if you're ever curious about what's possible, refer to the Object Model. – David Zemens Oct 13 '16 at 13:33
  • @Ralph That doesn't work for me. I have to worksheets, but nothing pops up. – Robby Oct 13 '16 at 15:47
  • As pointed out in the answer provided by @GarysStudent this will only count actual worksheets. This does not include charts, dialogs, or any such things. If you wish to include those then you'll have to use `If ThisWorkbook.Sheets.Count > 1 Then MsgBox "There is more than one object in this Excel file."` – Ralph Oct 13 '16 at 15:52
  • I do have two worksheets. There are two tabs at the bottom, but running this code doesn't do anything. – Robby Oct 13 '16 at 15:55
  • Which office are you running? – 0m3r Oct 13 '16 at 17:08
  • @Om3r Office 2010. – Robby Oct 13 '16 at 20:37
  • If you really do have two worksheets and running this code doesn't do anything then you are clearly doing something wrong since this is not possible. Did you disable macros? Did you copy the code into the wrong Excel file while executing? Maybe you copied the code into the `Personal.xlsb` file (if you have that) instead of the file you intended to? That's all I can think of right now. In essence I am saying that the code should work. There is not much room for errors with no more than one line of code. Hence, I am voting to close this post since the problem you experience cannot be reproduced. – Ralph Oct 13 '16 at 21:26
  • I did put it in my Personal.xlsb file. But I'm running it with a different workbook (with 2 worksheets) active. – Robby Oct 13 '16 at 21:33

3 Answers3

1

To get the number of worksheets within an open workbook, something like:

Sub qwerty()

    MsgBox "the number of worksheets in this workbook is: " & ThisWorkbook.Worksheets.Count

End Sub

This will exclude Charts, etc.
If you have multiple workbooks open, then something like:

    MsgBox "the number of worksheets in this workbook is: " & wb.Worksheets.Count

Where you would Set wb in a prior statement.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
1

To run it from Personal.xlsb then Try this 

Public Sub Count_Sheets()

    Debug.Print "You Have " & Application.Sheets.count & " Sheets " ' Immediate Window
    MsgBox "You Have " & Application.Sheets.count & " Sheets "

End Sub

Or use ActiveWorkbook.Sheets.count

0m3r
  • 12,286
  • 15
  • 35
  • 71
1

This is what ended up working best for me. It incorporates multiple answers in here to do what it does.

Sub CountSheets()
Dim mainWB  As Workbook
Dim mainWS  As Worksheet
Set mainWB = ActiveWorkbook
Set mainWS = mainWB.Sheets(1)

If mainWB.Sheets.Count > 1 Then MsgBox "There is more than one worksheet in this Excel file."
End Sub
Robby
  • 843
  • 3
  • 19
  • 53