VBA For Next Loops

Sub Simple_For_Next_1()
'*************************************************************
' Date          12/28/2018
' Developer:    Ray Mills
' Purpose       Demonstrate do loops
'               Change all lines to Caps
'*************************************************************
' create a worksheet object
Dim shtMine As Worksheet

' start in the right place ...
Range("A1").Select

' run through the list ...
For Each shtMine In Worksheets
    'do something here
     ActiveCell.Value=shtMine.Name
ActiveCell.Offset(1, 0).Select
Next shtMine

' move back to starting pt ...
Range("A1").Select

End Sub
Results VBA For Next Loop
In the code above I’ve demonstrated a VBA For Next Loop.  I have also introduced the concept of a collection e.g., Worksheets represent the collection of all sheets.  Here is what the code does:
1. Creates a new worksheet object
2. Moves us to cell A1
3. Loops through the worksheets collection
4. Posts each sheet’s name in the current or Activecell
5. Moves down one row
6. Returns us to cell A1

I ran the code in the my Build a Better Budget worksheet

If you were able to utilize what you learned here or improve the code,
please leave a comment Here

 

If you enjoyed this post with video or found it all helpful and would like to say hello and
 buy me a cup of coffee Click here

Raymond Mills MBA, MS
Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle,  Squel Server and Sybase to give up their secrets. Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” You can contact Ray @ 484 574-3190 or by emailing him Here

If you have a challenge with Excel, Access or Word and would like to speak with Ray,   You can get his contact details by clicking here: Contact Me

Comments are closed.