What is the syntax to loop through worksheets?

'Method 1 - looping using a For Each loop
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
    <your code here>
Next sht

'Method 2 - looping using a For loop
For i=1 To ThisWorkbook.Worksheets.Count
     <your code here, referencing Worksheets(i)>
Next
'example using both methods
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
     sht.Range("B2") = "Hello"
Next sht

For i=1 To ThisWorkbook.Worksheets.Count
     Worksheets(i).Range("B2") = "Hello"
Next

 

Advertisements

The danger of loops

Loops are easy to learn, simple, and thus, abundantly overused. Beginners to the VBA language often fall into the trap of overusing loops when much simpler code statements can be substituted. Be eager to continuously learn new code semantics, and not always fall back on loops.

(ex) The following example searches a column for empty slots and flags them as “empty”

‘example with loops
Dim searchRange as Range
For Each searchRange In Range(“A1:A15”)
    If IsEmpty(searchRange) Then searchRange = “Empty”
Next searchRange

‘example without loops
Range(“A1:A15”).SpecialCells(xlCellTypeBlanks) = “Empty

unnamed (4).jpgunnamed (3)