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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,712
Messages
5,512,994
Members
408,928
Latest member
Black Vinyl

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top