Worksheets.count with exemption

Olaf Jacobs

New Member
Joined
Jun 7, 2019
Messages
10
Office Version
365, 2019, 2010
Platform
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..
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
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.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
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
 

Olaf Jacobs

New Member
Joined
Jun 7, 2019
Messages
10
Office Version
365, 2019, 2010
Platform
Windows
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...
 

Olaf Jacobs

New Member
Joined
Jun 7, 2019
Messages
10
Office Version
365, 2019, 2010
Platform
Windows
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
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
 

Olaf Jacobs

New Member
Joined
Jun 7, 2019
Messages
10
Office Version
365, 2019, 2010
Platform
Windows
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.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
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
 

Olaf Jacobs

New Member
Joined
Jun 7, 2019
Messages
10
Office Version
365, 2019, 2010
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,259
Messages
5,467,624
Members
406,545
Latest member
puneet829

This Week's Hot Topics

Top