Loop through multiple WS, but not all WS.

rjwebgraphix

Well-known Member
Joined
May 25, 2010
Messages
588
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

mvptomlinson

Well-known Member
Joined
Mar 10, 2008
Messages
2,638
To loop through some sheets, you could use the Array function, like so:
Code:
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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.



Code:
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

Well-known Member
Joined
May 25, 2010
Messages
588
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,837
Messages
5,833,915
Members
430,244
Latest member
Ireland1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top