May 8, 2019

Two Methods to Loop through Worksheets

How to use two methods to loop through worksheets automatically using VBA. When we wish to copy or format data in multiple worksheets, we need to access them first. Watch the video below:

Two Methods to Loop through Worksheets

Here’s the complete VBA code to access all or multiple worksheets in a workbook to perform further actions:

Sub LoopWorksheet1()
Dim worksheetcount As Long
Dim i As Long

‘set worksheetcount equal to the number of worksheets in our active workbook
worksheetcount = ActiveWorkbook.Worksheets.Count

For i = 1 To worksheetcount

‘insert relevant code to make a range bold
‘we display the worksheet name via a message box
MsgBox ActiveWorkbook.Worksheets(i).Name

Next i
End Sub

Sub LoopWorksheets2()
‘declare worksheet object variable
Dim ws As Worksheet

‘looping process starts

For Each ws In Worksheets

‘insert code to do some action
‘display each sheet name

MsgBox ws.Name

Next
End Sub

Further reading:

Merge Data from Worksheets into Master Worksheet

Apply Autofilter Across Multiple Excel Worksheets

Watch this video on YouTube.

2 thoughts on “Two Methods to Loop through Worksheets

  1. hi sir

    i am happy leran to your classes , right now iam working back office job , it is not related data anlysis job ,but i have instrest to get job in data anlysis please help me which topics to cover anlystics.
    iam not aware of topics.

    1. Hi sir !
      Thanks for your wonderful tutorials.

      Am working in a school as an academic master. I have created an excel template whicha can also produce parent reports foe my students. I am now stuck because i am unable to print all the report cards because to change from one student to the next i used data validation

      Now am asking for your held to show me how i can write a VBA cade for printin all the report card in a single click.

Comments are closed.