• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

VBA For Each Loop

Home » VBA » VBA Logical Functions » VBA For Each Loop

By Sharmila Reddy Leave a Comment

VBA For Each Loop

Excel VBA For Each Loop

To move to the next step in VBA it is mandatory to understand the loops in VBA. A loop allows you to conduct the same kind of activity for many cells or objects in excel. In today’s article, we are going to concentrate on For Each Loop mechanism in VBA. For Each Loop allows us to go through all the collection of objects. Objects are nothing but Ranges, Worksheets, Charts, Workbooks, etc.

For example, You have 10 sheets in your workbook and you want to hide all the worksheets except the one you are in. Can you hide manually, yes you can but what if you have 100 sheets like that isn’t that a boring and time-consuming task to do.

What Does For Each Loop Do?

In VBA For Each Loop can loop through all the set collection of objects or items. A collection is nothing but “All the opened workbooks”, “All the worksheets in a workbook”, “All the collection of shapes and charts in the workbook”.

Using For Each Loop in vba we can go through all the objects and perform a similar set of activities. It will take into consideration of all the available specified objects and perform instructed activity in each object.

Let’s look at the syntax of FOR EACH LOOP in VBA.

For Each Object In Collection
           
     What to Do?

Next Object

Examples of For Each Loop in Excel VBA

Below are the examples of VBA For Each Loop in Excel.

You can download this VBA For Each Loop Template here – VBA For Each Loop Template

Example #1 – Insert Same Text in All the Sheets

We will see how to use FOR EACH LOOP in VBA with a simple example. Assume you have 5 worksheets in a workbook and you want to insert the word “Hello” in all the worksheets in cell A1.

VBA For Each Loop Example 1

We can do this with FOR EACH LOOP. One thing you need to remember here is we are actually performing this activity in each worksheet, not in the same worksheet. Follow below steps to write the VBA code for, FOR EACH LOPP

Popular Course in this category
Cyber Monday Sale
VBA Training (3 Courses, 12+ Projects) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
4.6 (247 ratings)
Course Price

View Course

Related Courses

Step 1: Start the excel macro.

Code:

Sub For_Each_Example1()

End Sub

VBA For Each Loop Example 1-1

Step 2: Since we are referring to the worksheets declare the variable as “Worksheet”.

Code:

Sub For_Each_Example1()

  Dim Ws As Worksheet

End Sub

VBA For Each Loop Example 1-2

Step 3: Now using FOR EACH LOOP we need to refer each worksheet in the active workbook.

Code:

Sub For_Each_Example1()

   Dim Ws As Worksheet

   For Each Ws In ActiveWorkbook.Worksheets

   Next Ws

End Sub

Example 1-3

Step 4: Now write what we want to do in each worksheet. In each worksheet, we need to put the word “Hello” in cell A1.

Code: 

Sub For_Each_Example1()

   Dim Ws As Worksheet

   For Each Ws In ActiveWorkbook.Worksheets
   Ws.Range("A1").Value = "Hello"
   Next Ws

End Sub

Example 1-4

Step 5: Now run this code manually through option or press shortcut key F5, it doesn’t matter how many sheets you have it will insert the Word “Hello” in all the worksheets.

VBA For Each Loop Example 1-5

Example #2 – Hide All the Sheets

As told earlier in the post what will if you have hundreds of sheets to hide except the one you are in. Using VBA For each loop, we can hide all the sheet in excel.

Step 1: Start the macro with your name.

Code:

Sub For_Each_Example2()

End Sub

VBA For Each Loop Example 2

Step 2: Declare the variable as “Ws”.

Code:

Sub For_Each_Example2()

  Dim Ws As Worksheet
 
End Sub

VBA For Each Loop Example 2-1

Step 3: Now in each worksheet what you need to do. We need to hide the sheet.

Code:

Sub For_Each_Example2()

  Dim Ws As Worksheet

  For Each Ws In ActiveWorkbook.Worksheets
  Ws.Visible = xlSheetVeryHidden
  Next Ws

End Sub

Example 2-2

Step 4: But if you run the above code it will try to hide all the sheets but excel needs at least one the sheet to be visible. So we need to tell which sheet to not hide.

Code:

Sub For_Each_Example2()

  Dim Ws As Worksheet

  For Each Ws In ActiveWorkbook.Worksheets

  If Ws.Name <> "Main Sheet" Then
  Ws.Visible = xlSheetVeryHidden
  End If
  Next Ws

End Sub

Example 2-3

The operator symbol <> means not equal to in VBA.

So code says when you are looping through all the worksheets in the active workbook hide only if the sheet name is not equal to the sheet name Main Sheet

This can be done by using the IF statement in VBA. Write the code as IF Ws.Name <> “Main Sheet” Then hide or if it is equal to the sheet name “Main Sheet” then don’t hide.

Step 5: Now run the code using F5 key or manually then, it will hide all the worksheet except the one named as “Main Sheet”.

VBA For Each Loop Example 2-4

Example #3 – Unhide All the Sheets

We have seen how to hide all sheets except the one we are in. Similarly, we can unhide all the worksheets as well.

We just need to change the code from xlSheetVeryHidden to xlSheetVisible.

Code:

Sub For_Each_Example3()

Dim Ws As Worksheet

For Each Ws In ActiveWorkbook.Worksheets
Ws.Visible = xlSheetVisible
Next Ws

End Sub

VBA For Each Loop Example 3

Here we don’t need IF condition because we are Un-hiding all the sheets. If you don’t want to un-hide any specific sheet then you can use IF condition and supply the sheet name.

Example 3-1

Example #4 – Protect and UnProtect All the Sheets

Protect All Sheets: We can protect all the sheets in the workbook with just a piece of code. All the code is the same only thing we need to do here is instead of Ws.Visible we need to put the code Ws.Protect and type the password.

Code:

Sub For_Each_Example4()

  Dim Ws As Worksheet

  For Each Ws In ActiveWorkbook.Worksheets
    Ws.Protect Password:="Excel@2019"
  Next Ws

End Sub

VBA For Each Loop Example 4

Unprotect All the Sheets: On a similar note, using vba we can also unprotect all the protected sheets in the workbook. We just need to put the word Unprotect and password.

Code:

Sub For_Each_Example6()

  Dim Ws As Worksheet

  For Each Ws In ActiveWorkbook.Worksheets
  Ws.Unprotect Password:="Excel@2019"
  Next Ws

End Sub

VBA For Each Loop Example 4-1

Things to Remember

  • Each is for collection of objects.
  • It will consider all the specified objects in the specified workbook.
  • While declaring the variable we need to which object we are referring to. For example Worksheet, Workbook, Chart, etc.

Recommended Articles

This has been a guide to VBA For Each Loop. Here we learn how to use VBA For Each Loop to 1) Insert Text, 2) Hide or Unhide sheets and 3) Protect and Unprotect Workbook in excel along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

  • VBA Examples to Remove Duplicate Values
  • VBA Split Examples
  • Tutorial of VBA with Examples
  • End Property in VBA
  • Excel VBA Call Sub
  • List of VBA Functions
  • Do Until Loop in VBA
  • For Next Loop in VBA
  • Max VBA example
14 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: VBA, VBA Logical Functions

Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Download VBA For Each Loop Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Limited Period Offer - VBA Training Course (6 courses, 35+ hours video) View More