VBA: Help needed to understand the logic behind looping through workbooks using for loops and case statement

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
Greetings to you all,
I have this piece of code here that I need to understand properly so that I don't end up misusing it during my implementation.
I have 5 worksheets that I will like to loop through and do something afterwards.

They are having the same layout and might have equal rows of data or different rows of data.

Now my questions:
1. How is the code executing? Does it run any code under the first condition against all the 5 sheets?
2. If say, I have this line
Code:
 lr = sht.cells(Rows.Count, "A").End (xlUp).Row
under the case statement, will it record for the first sheet the to the last sheet?
3. Assuming that I want to use real sheet names (actual sheet names) instead of their code names, how should that one be?

Code:
Dim sht As Worksheet 

For Each sht In Worksheets
     Select Case sht.CodeName 
        Case "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5"

        Case Else
     End Select 
Next sht

Thanks for your time.

Have a wonderful moment.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Anything inside the For Each block will be evaluated at each pass through. If you want to use the sheet name, use sht.Name instead of sht.Codename
 
Upvote 0
Solution
Anything inside the For Each block will be evaluated at each pass through. If you want to use the sheet name, use sht.Name instead of sht.Codename
Thanks @RoryA for clearing my doubts.

Enjoy the rest of your day.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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
Back
Top