Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Worksheets.count with exemption
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Worksheets.count with exemption

    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..

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,833
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Worksheets.count with exemption

    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.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  3. #3
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,833
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Worksheets.count with exemption

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,833
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Worksheets.count with exemption

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  5. #5
    New Member
    Join Date
    Jun 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Worksheets.count with exemption

    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...

  6. #6
    New Member
    Join Date
    Jun 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Worksheets.count with exemption

    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

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,833
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Worksheets.count with exemption

    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.
    Quote Originally Posted by Olaf Jacobs View Post
    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  8. #8
    New Member
    Join Date
    Jun 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Worksheets.count with exemption

    Quote Originally Posted by My Aswer Is This View Post
    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.

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,833
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Worksheets.count with exemption

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  10. #10
    New Member
    Join Date
    Jun 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Worksheets.count with exemption

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •