Excel VBA For Each Loop

Situatie

In VBA, it is mandatory to understand the loops. A loop allows you to conduct the same activity for many cells or objects in excel. VBA For Each Loop can loop through all the set collections of objects or items. For example, a group means “All the opened workbooks,” “All the worksheets in a workbook,” and “All the collection of shapes and charts in the workbook.”

Solutie

Pasi de urmat

For Each Object In Collection
           
     What to Do?

Next Object

Insert Same Text in All the Sheets

We will see how to use FOR EACH 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. You must remember that we are performing this activity on each worksheet, not on the same worksheet. Follow the below steps to write the VBA code.

Step 1: Start the excel macro.

Sub For_Each_Example1()

End Sub

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

Code:

Sub For_Each_Example1()

  Dim Ws As Worksheet

End Sub

Step 3: Now, using FOR EACH LOOP, we need to refer to 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

Step 4: Write what we want to do on 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

Step 5: Run this code manually through the option or press shortcut key F5. It does not matter how many sheets you have; it will insert the word “Hello” in all the worksheets.

Tip solutie

Permanent

Follow Us