CODEWITHSUNDEEP
excelvba
SadMrFrown
SadMrFrown

Reputation: 157

Perform a loop based on the worksheet name being based on a string

I want to perform specific tasks based on the worksheet name. In order to loop through all these worksheets I usually define an array of strings and then loop through this array like so:

Option Explicit
dim i as integer
dim arr_test as variant
dim wb as workbook
dim ws as worksheet
arr_test = Array("Test1", "Test2", "Test3")
set wb = application.activeworkbook
for i = 0 to 2
 set ws = wb.sheets(arr_test(i))
 'do something
next i

My question is, is there a better way to do this? (Ie one that uses less lines and is more efficient wrt processing speed).

Thanks!

Upvotes: 0

Views: 36

Answers (2)

pgSystemTester
pgSystemTester

Reputation: 9932

In answer to your question, there's not a major way to improve your code. I would agree with the first answer's approach by looping through the sheets to match the three names as this can defend against capitalization issues by doing something like: UCASE("TEST1") = UCASE(ws.name)

Here's a slightly cleaner version of your code that has less lines. It's also dynamic so that if you add another test, you don't need to change the line of code of the loop because it uses the Ubound option.

Dim i As Long, arr_test() As Variant, wb As Workbook, ws As Worksheet

arr_test = Array("Test1", "Test2", "Test3")

Set wb = Application.ActiveWorkbook

For i = 0 To UBound(arr_test)
    Set ws = wb.Sheets(arr_test(i))
    'do something
Next i

Upvotes: 1

Harassed Dad
Harassed Dad

Reputation: 4714

 Dim ws as worksheet
 For each ws in ActiveWorkbook.Worksheets
     Select case ws.name
        Case "Test1", "Test2", "Test3"
            'do something to ws
     End Select
  next ws

If you want to do something to all sheets then you can omit the select case and it's even faster

Upvotes: 2

Related Questions