Loop through multiple WS, but not all WS.

[rjwebgraphix]

I'm just looking for a suggestion to get me started. I'm looking to loop through 11 different worksheets, but not all worksheets in the workbook.

In the VBA editor they read like this...

Sheet14 (E00)
Sheet22 (PR)
Sheet23 (W10)
Sheet24 (E02)
Sheet25 (E03)
Sheet26 (E04)
Sheet27 (E14)
Sheet28 (P03)
Sheet29 (P05)
Sheet30 (W02)
Sheet31 (W08)

There are sheets in between 14 and 22 that I don't want to loop through. This is also not the order I'd prefer to loop through them. I'd prefer to loop them in the order they are in the workbook which is:

PR, E00, E02, E04, E14, P03, P05, W02, W08, W10

Is there an easy way to just say loop through them in order from PR to W10? I was thinking of just doing an array with them in that order and loop through the array, but I'm not the greatest with array's and rather avoid that route if there's a better way. I've found many articles are looping through all WS's, but haven't found one yet for a specific set of WS.

Thanks,

RJ

 

[mvptomlinson]

To loop through some sheets, you could use the Array function, like so:

Sub someSheets()
Dim ws As Worksheet
For Each ws In Sheets(Array("Sheet1", "Sheet3", "Sheet5"))
    ws.Range("A2") = ws.Name
Next ws
End Sub

 

[hiker95]

rjwebgraphix,


In the VBA Editor, if you were to step thru the code with the F8 key, each sheet in the array will be activated in the order that they are located in the array.

Option Explicit
Option Base 1
Sub LoopMySheets()
' hiker95, 07/22/2010, http://www.mrexcel.com/forum/showthread.php?t=483202
Dim MySheets, a As Long
Application.ScreenUpdating = False
MySheets = Array("PR", "E00", "E02", "E04", "E14", "P03", "P05", "W02", "W08", "W10")
For a = LBound(MySheets) To UBound(MySheets)
  With Worksheets(MySheets(a))
    
    'If you step thru the code in the VBA Editor
    '  the sheets in the array will be activated
    '  in the order they are listed in the array.
    
    .Activate
    
    'The rest of your code goes here.
    
    
  End With
Next a
Application.ScreenUpdating = True
End Sub

 

[rjwebgraphix]

Application.ScreenUpdating = True/False

Does this speed up processing time on each ws? I've left screen updating on intentionally, because it acts as a visual for the user of where the progress is on a macro that takes between 1-10 minutes to run depending on how far we are into a month.

 

[hiker95]

rjwebgraphix,

Does this speed up processing time on each ws? I've left screen updating on intentionally, because it acts as a visual for the user of where the progress is on a macro that takes between 1-10 minutes to run depending on how far we are into a month.

In this case I would not turn off screen updating.


Yes, Application.ScreenUpdating = False, can speed up processing.


If you were to turn it off at the beginning of your code, then you would turn it back on at the end of your code.