Basic Excel VBA Macro: Loop through all worksheets

What is an Excel macro? If you perform a task repeatedly, you can take advantage of a macro to automate the task. A macro (or makro to some people) is a series of functions, written in a scripting language, that you group in a single command to perform the requested task automatically. You can record a macro but it is highly recommended that you learn VBA in order to write and use macros of your own creation. I will help you learn VBA through a series of Excel macro tutorials, starting with how to scroll through all the tabs in a workbook.

In this Excel macro example, I want to loop through all worksheets in order to copy a specified range of cells and then delete the contents of a couple of other cells. To loop through all worksheets I will utilize a function call a For Loop. Here's a programming syntax tip for you: Add comments to your statements using an apostrophe ('), either at the beginning of a separate line, or at the end of a statement. It is recommended that you add comments wherever possible, to make your scripts easier to understand and maintain, especially if another user has to make changes to it later on down the road. I use comments in my example code below to help explain each step of the program. I have indented all the code which takes place inside the for loop.

Sub WorksheetLoop()

   Dim WS_Count As Integer
   Dim I As Integer
   Application.ScreenUpdating = False

   ' Set WS_Count equal to the number of worksheets in the active workbook.
   WS_Count = ActiveWorkbook.Worksheets.Count

           ' Begin the loop through all worksheets.
           For I = 1 To WS_Count

              'message box displaying the name of the worksheet
              MsgBox ActiveWorkbook.Worksheets(I).Name
     
            'active sheet i    
            ActiveWorkbook.Worksheets(I).Activate

        'select the old data and insert new rows
            Rows("5:39").Select
            Selection.Copy
            Rows("5:5").Select
            Selection.Insert Shift:=xlDown
            Range("C39").Select
       
        'delete old data
        Range("H17:AA22").Select
            Range("W17").Activate
            Application.CutCopyMode = False
            Selection.ClearContents
            Range("AB11:AB39").Select
            Range("AB39").Activate
            Selection.ClearContents


   Next I
   
 Application.ScreenUpdating = True

End Sub

Now you can answer the question what is a macro! Please comment below if you have any questions.

Learn how to export to Excel from CATIA.