Worksheets.count with exemption

Olaf Jacobs

New Member
Joined
Jun 7, 2019
Messages
10
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
Hi all,

Im looking for a way to filter out the two sheets of the worksheets.count

I use VBA to create an new sheet and then to rename this sheet, so vba counts the amount of sheets and ads one etc, but I also have two sheets in the workbook with an name and this is also taking in the total sum of the count. So how do i filter these two sheets out ?
The name is "stop" and "performance"

Thanks in advance..
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
To add a new sheet why do you need to count the sheets?

And show me the Vba script your using to add a new sheet.
 
Upvote 0
Try this:
Code:
Sub Count_Sheets()
'Modified 7/4/2019 2:27:51 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim x As Long
x = 0
For i = 1 To Sheets.Count
    If Sheets(i).Name <> "stop" And Sheets(i).Name <> "performance" Then x = x + 1
Next
MsgBox "You have " & x & " Sheets"
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Actually if you always have two sheets you want to exclude from the sheet count you can use this script:

Code:
Sub Count_Sheets()
'Modified 7/4/2019 2:33:51 AM  EDT
MsgBox "You have " & Sheets.Count - 2 & " Sheets"
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi,

I'm using the following code to run an new sheet.
Code:
Sub Copy_sheet_auto()

Dim newname As String
Dim sh As Worksheet
    newname = ActiveSheet.Name
Duplicatesearch:
    For Each sh In Worksheets
    If newname = ("") Then Exit Sub
    If UCase(sh.Name) = UCase(newname) Then
    newname = ActiveWorkbook.Sheets.Count + 1
    GoTo Duplicatesearch
    End If
    Next sh
    ActiveSheet.Copy Worksheets(1)
    ActiveSheet.Name = newname
    ActiveSheet.Move after:=Worksheets(Worksheets.Count)

End Sub

This works great but im struggling to get these two sheets (stop and performance) out...
 
Upvote 0
I use this same copy vba for an other workbook to and this is a copy/paste to this sheet, already cleaned up and deleted what I dont need, so the vba I actually need is
Code:
Sub Copy_sheet_auto()

Dim newname As String
Dim sh As Worksheet
    newname = ActiveSheet.Name
    newname = ActiveWorkbook.Sheets.Count + 1
    ActiveSheet.Copy Worksheets(1)
    ActiveSheet.Name = newname
    ActiveSheet.Move after:=Worksheets(Worksheets.Count)

End Sub
 
Upvote 0
Well it sounds like to me you have sorted out what you want. I see no question here. Glad to see you now have what you need. Take care.
I use this same copy vba for an other workbook to and this is a copy/paste to this sheet, already cleaned up and deleted what I dont need, so the vba I actually need is
Code:
Sub Copy_sheet_auto()

Dim newname As String
Dim sh As Worksheet
    newname = ActiveSheet.Name
    newname = ActiveWorkbook.Sheets.Count + 1
    ActiveSheet.Copy Worksheets(1)
    ActiveSheet.Name = newname
    ActiveSheet.Move after:=Worksheets(Worksheets.Count)

End Sub
 
Upvote 0
Well it sounds like to me you have sorted out what you want. I see no question here. Glad to see you now have what you need. Take care.

This is the code i use to make/create an new sheet put it at the end of the workbook. What I like to do is to exclude the two sheets with the name stop and performance in the sheet count.
If i start this workbook it starts of with three sheets, the first sheet will be the "performance" sheet, the second will be the "stop" sheet, from there there is the first sheet named "1" and this is what needs to be copied and after each report there will be an new report created. If I use the sheet.count function +1 it will count already 3 sheets and numbers the new sheet to 4 and continues from there. So to make thing right i would like to exclude the two sheets ("performance" and "stop") from the count so it only counts the sheets with numbers in the sheet name.
 
Upvote 0
I'm sorry maybe someone else here on the Forum will see this and be able to help you.
I'm still confused.

I prefer users tell me what there ultimate goal is and let me figure out how to achieve the goal.

I do not understand why you need to use all the code you have showed here just to create a new sheet.

And then your saying if I understand you correctly you need more code to achieve your ultimate goal.

When I want to make a new sheet I use code like this:
Code:
Sub Make_New_Sheet()
'Modified  7/4/2019  11:38:20 AM  EDT
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Alpha"
End Sub
 
Upvote 0
Fair enough i get what you're saying, i use for the sheet name sheet.count + 1 and from there it will continue counting. I only want to exclude the two sheets out of this count.
Sheers anyway for you're help.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top