VBA to dynamically decide no of worksheets and perform calculations on them












0















Is there any way with VBA to decide number of worksheets in a workbook and perform calculations on predefined range of cells in those worksheets. The worksheets in the workbook may change as new worksheets may get added.
I have this requirement where worksheets are regularly get added to the workbook and we need to calculate sum of specific range of cells. I googled it but did not find any solution not even here at Stakoveflow










share|improve this question




















  • 1





    Try ThisWorkbook.Sheets.Count

    – PeterT
    Dec 30 '18 at 15:22
















0















Is there any way with VBA to decide number of worksheets in a workbook and perform calculations on predefined range of cells in those worksheets. The worksheets in the workbook may change as new worksheets may get added.
I have this requirement where worksheets are regularly get added to the workbook and we need to calculate sum of specific range of cells. I googled it but did not find any solution not even here at Stakoveflow










share|improve this question




















  • 1





    Try ThisWorkbook.Sheets.Count

    – PeterT
    Dec 30 '18 at 15:22














0












0








0








Is there any way with VBA to decide number of worksheets in a workbook and perform calculations on predefined range of cells in those worksheets. The worksheets in the workbook may change as new worksheets may get added.
I have this requirement where worksheets are regularly get added to the workbook and we need to calculate sum of specific range of cells. I googled it but did not find any solution not even here at Stakoveflow










share|improve this question
















Is there any way with VBA to decide number of worksheets in a workbook and perform calculations on predefined range of cells in those worksheets. The worksheets in the workbook may change as new worksheets may get added.
I have this requirement where worksheets are regularly get added to the workbook and we need to calculate sum of specific range of cells. I googled it but did not find any solution not even here at Stakoveflow







excel vba excel-vba






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 7 at 8:57









Pᴇʜ

21.7k42750




21.7k42750










asked Dec 30 '18 at 15:07









user3405976user3405976

25117




25117








  • 1





    Try ThisWorkbook.Sheets.Count

    – PeterT
    Dec 30 '18 at 15:22














  • 1





    Try ThisWorkbook.Sheets.Count

    – PeterT
    Dec 30 '18 at 15:22








1




1





Try ThisWorkbook.Sheets.Count

– PeterT
Dec 30 '18 at 15:22





Try ThisWorkbook.Sheets.Count

– PeterT
Dec 30 '18 at 15:22












1 Answer
1






active

oldest

votes


















1














Loop Through Worksheets With Exceptions List





  • Performs operations in the 'Code in here' section on all worksheets except the ones in the Exception Comma-Separated List.

  • The difference between the two versions is that the first version uses the object control variable ws, but the second version doesn't need it, but uses the control variable i and the .Count property of the Worksheets collection.

  • If you don't have any exceptions i.e. you want to perform operations on all worksheets, then just leave cExceptions as "".




The For Each Next Approach



Sub WorksheetsForEach()

' Exceptions Comma-Separated List
Const cExceptions As String = "Sheet1,Sheet2"

Dim ws As Worksheet ' Current Worksheet
Dim vntExceptions As Variant ' Exceptions Array
Dim j As Integer ' Exceptions Counter

vntExceptions = Split(cExceptions, ",")

For Each ws In Worksheets
With ws
For j = 0 To UBound(vntExceptions)
If .Name = vntExceptions(j) Then
Exit For
End If
Next
If j > UBound(vntExceptions) Then
' Code in here e.g.
Debug.Print .Name

End If
End With
Next

End Sub


The For Next Approach



Sub WorksheetsForNext()

' Exceptions Comma-Separated List
Const cExceptions As String = "Sheet1,Sheet2"

Dim vntExceptions As Variant ' Exceptions Array
Dim i As Integer ' Worksheets Counter
Dim j As Integer ' Exceptions Counter

vntExceptions = Split(cExceptions, ",")

For i = 1 To Worksheets.Count
With Worksheets(i)
For j = 0 To UBound(vntExceptions)
If .Name = vntExceptions(j) Then
Exit For
End If
Next
If j > UBound(vntExceptions) Then
' Code in here e.g.
Debug.Print .Name

End If
End With
Next

End Sub





share|improve this answer

























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53978726%2fvba-to-dynamically-decide-no-of-worksheets-and-perform-calculations-on-them%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    1














    Loop Through Worksheets With Exceptions List





    • Performs operations in the 'Code in here' section on all worksheets except the ones in the Exception Comma-Separated List.

    • The difference between the two versions is that the first version uses the object control variable ws, but the second version doesn't need it, but uses the control variable i and the .Count property of the Worksheets collection.

    • If you don't have any exceptions i.e. you want to perform operations on all worksheets, then just leave cExceptions as "".




    The For Each Next Approach



    Sub WorksheetsForEach()

    ' Exceptions Comma-Separated List
    Const cExceptions As String = "Sheet1,Sheet2"

    Dim ws As Worksheet ' Current Worksheet
    Dim vntExceptions As Variant ' Exceptions Array
    Dim j As Integer ' Exceptions Counter

    vntExceptions = Split(cExceptions, ",")

    For Each ws In Worksheets
    With ws
    For j = 0 To UBound(vntExceptions)
    If .Name = vntExceptions(j) Then
    Exit For
    End If
    Next
    If j > UBound(vntExceptions) Then
    ' Code in here e.g.
    Debug.Print .Name

    End If
    End With
    Next

    End Sub


    The For Next Approach



    Sub WorksheetsForNext()

    ' Exceptions Comma-Separated List
    Const cExceptions As String = "Sheet1,Sheet2"

    Dim vntExceptions As Variant ' Exceptions Array
    Dim i As Integer ' Worksheets Counter
    Dim j As Integer ' Exceptions Counter

    vntExceptions = Split(cExceptions, ",")

    For i = 1 To Worksheets.Count
    With Worksheets(i)
    For j = 0 To UBound(vntExceptions)
    If .Name = vntExceptions(j) Then
    Exit For
    End If
    Next
    If j > UBound(vntExceptions) Then
    ' Code in here e.g.
    Debug.Print .Name

    End If
    End With
    Next

    End Sub





    share|improve this answer






























      1














      Loop Through Worksheets With Exceptions List





      • Performs operations in the 'Code in here' section on all worksheets except the ones in the Exception Comma-Separated List.

      • The difference between the two versions is that the first version uses the object control variable ws, but the second version doesn't need it, but uses the control variable i and the .Count property of the Worksheets collection.

      • If you don't have any exceptions i.e. you want to perform operations on all worksheets, then just leave cExceptions as "".




      The For Each Next Approach



      Sub WorksheetsForEach()

      ' Exceptions Comma-Separated List
      Const cExceptions As String = "Sheet1,Sheet2"

      Dim ws As Worksheet ' Current Worksheet
      Dim vntExceptions As Variant ' Exceptions Array
      Dim j As Integer ' Exceptions Counter

      vntExceptions = Split(cExceptions, ",")

      For Each ws In Worksheets
      With ws
      For j = 0 To UBound(vntExceptions)
      If .Name = vntExceptions(j) Then
      Exit For
      End If
      Next
      If j > UBound(vntExceptions) Then
      ' Code in here e.g.
      Debug.Print .Name

      End If
      End With
      Next

      End Sub


      The For Next Approach



      Sub WorksheetsForNext()

      ' Exceptions Comma-Separated List
      Const cExceptions As String = "Sheet1,Sheet2"

      Dim vntExceptions As Variant ' Exceptions Array
      Dim i As Integer ' Worksheets Counter
      Dim j As Integer ' Exceptions Counter

      vntExceptions = Split(cExceptions, ",")

      For i = 1 To Worksheets.Count
      With Worksheets(i)
      For j = 0 To UBound(vntExceptions)
      If .Name = vntExceptions(j) Then
      Exit For
      End If
      Next
      If j > UBound(vntExceptions) Then
      ' Code in here e.g.
      Debug.Print .Name

      End If
      End With
      Next

      End Sub





      share|improve this answer




























        1












        1








        1







        Loop Through Worksheets With Exceptions List





        • Performs operations in the 'Code in here' section on all worksheets except the ones in the Exception Comma-Separated List.

        • The difference between the two versions is that the first version uses the object control variable ws, but the second version doesn't need it, but uses the control variable i and the .Count property of the Worksheets collection.

        • If you don't have any exceptions i.e. you want to perform operations on all worksheets, then just leave cExceptions as "".




        The For Each Next Approach



        Sub WorksheetsForEach()

        ' Exceptions Comma-Separated List
        Const cExceptions As String = "Sheet1,Sheet2"

        Dim ws As Worksheet ' Current Worksheet
        Dim vntExceptions As Variant ' Exceptions Array
        Dim j As Integer ' Exceptions Counter

        vntExceptions = Split(cExceptions, ",")

        For Each ws In Worksheets
        With ws
        For j = 0 To UBound(vntExceptions)
        If .Name = vntExceptions(j) Then
        Exit For
        End If
        Next
        If j > UBound(vntExceptions) Then
        ' Code in here e.g.
        Debug.Print .Name

        End If
        End With
        Next

        End Sub


        The For Next Approach



        Sub WorksheetsForNext()

        ' Exceptions Comma-Separated List
        Const cExceptions As String = "Sheet1,Sheet2"

        Dim vntExceptions As Variant ' Exceptions Array
        Dim i As Integer ' Worksheets Counter
        Dim j As Integer ' Exceptions Counter

        vntExceptions = Split(cExceptions, ",")

        For i = 1 To Worksheets.Count
        With Worksheets(i)
        For j = 0 To UBound(vntExceptions)
        If .Name = vntExceptions(j) Then
        Exit For
        End If
        Next
        If j > UBound(vntExceptions) Then
        ' Code in here e.g.
        Debug.Print .Name

        End If
        End With
        Next

        End Sub





        share|improve this answer















        Loop Through Worksheets With Exceptions List





        • Performs operations in the 'Code in here' section on all worksheets except the ones in the Exception Comma-Separated List.

        • The difference between the two versions is that the first version uses the object control variable ws, but the second version doesn't need it, but uses the control variable i and the .Count property of the Worksheets collection.

        • If you don't have any exceptions i.e. you want to perform operations on all worksheets, then just leave cExceptions as "".




        The For Each Next Approach



        Sub WorksheetsForEach()

        ' Exceptions Comma-Separated List
        Const cExceptions As String = "Sheet1,Sheet2"

        Dim ws As Worksheet ' Current Worksheet
        Dim vntExceptions As Variant ' Exceptions Array
        Dim j As Integer ' Exceptions Counter

        vntExceptions = Split(cExceptions, ",")

        For Each ws In Worksheets
        With ws
        For j = 0 To UBound(vntExceptions)
        If .Name = vntExceptions(j) Then
        Exit For
        End If
        Next
        If j > UBound(vntExceptions) Then
        ' Code in here e.g.
        Debug.Print .Name

        End If
        End With
        Next

        End Sub


        The For Next Approach



        Sub WorksheetsForNext()

        ' Exceptions Comma-Separated List
        Const cExceptions As String = "Sheet1,Sheet2"

        Dim vntExceptions As Variant ' Exceptions Array
        Dim i As Integer ' Worksheets Counter
        Dim j As Integer ' Exceptions Counter

        vntExceptions = Split(cExceptions, ",")

        For i = 1 To Worksheets.Count
        With Worksheets(i)
        For j = 0 To UBound(vntExceptions)
        If .Name = vntExceptions(j) Then
        Exit For
        End If
        Next
        If j > UBound(vntExceptions) Then
        ' Code in here e.g.
        Debug.Print .Name

        End If
        End With
        Next

        End Sub






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Dec 30 '18 at 21:17

























        answered Dec 30 '18 at 20:41









        VBasic2008VBasic2008

        2,5912414




        2,5912414






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53978726%2fvba-to-dynamically-decide-no-of-worksheets-and-perform-calculations-on-them%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas