Thanks Marek!

Thanks Michael! Those are great suggestions for polishing up the report.

Great question about loops. We used a For Next loop to loop through a collection of numbers in this video. The For Each loop is used when we are looping through a collection of objects. The following code would loop through each worksheet object in the workbook.

Dim ws as Worksheet

For Each ws in Activeworkbook.Worksheets

Next ws

This loop would have also worked for the summary sheets macro. However, we would have had to declare another variable to hold a number and then add to that number within each iteration of the loop.

Dim ws as Worksheet
Dim lRow as Long

For Each ws in Activeworkbook.Worksheets
Range(“A3”).Offset(lRow).Value = ws.Name
lRow = lRow + 1
Next ws

Picking the one to use might also depend on what you are doing with the worksheets that you are looping. If you are modifying the properties of the worksheet then you might want to use the For Each with a variable like ws because you will get the intellisense for ws. Each time you type ws. you will get the dropdown that contains all the properties and methods for ws (the worksheet object). This is nice if your macro includes tasks like renaming sheets, changing tab color, visible/hidden, protection, etc.

I hope that helps. In my upcoming VBA Pro Course I have three videos that go into the different types of For loops in more detail.

Thanks again Michael!

Great question Carole! There are a few different ways to do that. I have an article that explains different ways to find the last used cell in a worksheet. One of these approaches will work if your total is in the last row of the spreadsheet. If not, you will need to determine some other logic for the total row. For example, maybe that row contains the word “Total” in column A. You can use the Range.Find method to find that value, then offset a certain number of columns to locate the cell you want to reference in the summary sheet. Check it out and let me know if you have any questions. Thanks!

Hi Graeme,
Great question. Yes, that is absolutely possible. One way to do it is to use an IF statement in the loop to check for the summary sheet. In the code below I added the IF statement to the macro. This will test if the current sheet in the loop is NOT named Summary. The less than and greater than symbols together (<>) is the logical operators for NOT. Please let me know if you have any questions. Thanks!

For lSht = 1 To lCount
     IF Worksheets(lSht).Name <> "Summary" Then
        Range("A3").Offset(lSht, 0).Value = Worksheets(lSht).Name
        Range("A3").Offset(lSht, 1).Formula = "='" & Worksheets(lSht).Name & "'!D10"
        ActiveSheet.Hyperlinks.Add Anchor:=Range("A3").Offset(lSht, 0), _
                                    Address:="", _
                                    SubAddress:="'" & Worksheets(lSht).Name & "'!A3"
    End IF
Next lSht

Hi Denis,
Great question. For the hyperlink I used the Insert Hyperlinks menu. You can get to it by right-clicking a cell and selecting Hyperlink… (Ctrl+K on the keyboard). I basically recorded this action with the macro recorder to produce the code. The code that the macro recorder produced was static, meaning it referenced the sheet name and range I selected while recording. I then changed the code to reference the sheet number in the loop, and also added the Offset property. When I get some time I will record another video that explains both questions in more detail.

Please let me know if you have any questions. Thanks!

Thank you Dragan! You made my day! 🙂

Hi Keith,
Wow that is a lot of pivot tables on one sheet! 🙂 For those types of tasks you can use a For Next Loop to loop through all the objects in the collection. For pivot tables you can loop through all the pivot tables on the sheet. Here is some sample code
Dim pt as PivotTable
Dim sName as String

For each pt in Activesheet.PivotTables
sName = pt.Name
'Add code for meta data
Next pt

The above code will loop through all the pivot tables on the active sheet. You will then need to loop through the pivot fields within each pivot table to get some of the other meta data. Here is an article that explains more about pivot fields and pivot items.

There is definitely a lot to learn with pivot tables and VBA. The good news is that most of the properties of a pivot table are supported in the object model, making it possible to automate a lot of common pivot table tasks.

I hope that helps get you started. Thanks!

Thanks Dennis! I’m happy to hear you are learning VBA.