Loop using array with predifned sheets - VBA

Question

I am new to the site and new to vba coding and very new to loops, currently i am trying to summarize the data in a workbook which has total 25 sheets and these sheets are not fixed based on the issues we have the list of sheets maximum of 25 (2 (View Recon & Open Items) are main sheets on which my codes are run and add new sheets upto additional 23 sheets). I want to summarize the data of 10 sheets which were generated using view recon in Summary tab from ROW 4. For example sheet names (sheet2, 5, 7, 9, 10, 12, 14, 17, 18, 19), here sheet names are fixed and number of sheets are not fixed it can be 1-10.

I tried using - this is not working for me as i might have defined incorrectly

Dim sh As Worksheet

For Each sh In Sheets(Array("Sheet2", "Sheet5", "Sheet7", "Sheet9", "Sheet10", "Sheet12", "Sheet14", "Sheet17", "Sheet18", "Sheet19"))

Then my code to summarize it - which is working fine when i do it separately but i am not able to define and use with array, would be helpful if any of you can help me to get it corrected.

Happy weekend!

Thanks for your help!

Regards Suresh

score 1 · Accepted Answer · 2015-11-15 08:43:30Z

1

Use the Worksheet .Names as strings in a variant array. As you progress through each in turn, define your worksheet in a With ... End With statement.

Dim w As Long, vWSs As Variant

vWSs = Array("Sheet2", "Sheet5", "Sheet7", "Sheet9", "Sheet10", "Sheet12", "Sheet14", "Sheet17", "Sheet18", "Sheet19")

For w = LBound(vWSs) To UBound(vWSs)
    With Worksheets(vWSs(w))
        'here you will use .Range("A1") or .Cells(r, c)
        'the prefix period means the parent worksheet is
        'the one currently defined by the With ... End With
    End With
Next w

That is my own preferred method. As far as your own For Each sh In ... the same could be used like this.

Dim sh As Worksheet

For Each sh In Sheets(Array("Sheet2", "Sheet5", "Sheet7", "Sheet9", "Sheet10", "Sheet12", "Sheet14", "Sheet17", "Sheet18", "Sheet19"))
    With sh
         'Use .Range or .Cells here
    End With
Next sh

edited Nov 15, 2015 at 8:43

answered Nov 15, 2015 at 8:35

user4039065

Sign up to request clarification or add additional context in comments.

4 Comments

suresh7860 Over a year ago

Thanks Expert but i am not able to get the code corrected, would be helpful if you can see this, but i am not able to post it fully will try to use asnwer and post it

user4039065 Over a year ago

What are the name(s) of the worksheets in the name tabs at the bottom of the worksheet window?

user1016274 Over a year ago

@Jeeped: your second code example is exactly the same as OP's code, namely looping over an array of strings. Where's the improvement? The fault must be with something else, or do I miss the obvious?

user4039065 Over a year ago

@user1016274 - The code in the question will work as long as the sh variable is properly referenced. I was offering a way to refer to the worksheet within each iteration of the loop without repeatedly sh variable. With no working code that referred to an actual worksheet, the OP could have been using Range("A1") and expecting that to reference the correct parent worksheet.

Collectives™ on Stack Overflow

Loop using array with predifned sheets - VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related