How to Use VBA

In today’s email:

1. Excel basics

2. CSV files

3. XLSX vs XLSM files

4. Saving to Desktop vs Cloud

TRIVIA

MAIN ARTICLE

Video walkthrough:

Introduction

Large Excel workbooks can become unwieldy quickly, especially when dealing with dozens of worksheets for financial reports or inventory tracking.

Creating a central directory of all worksheet names not only saves time in navigation but also provides a clear overview of your workbook's structure.

Project Overview

Today we'll build a simple VBA macro that automatically creates and updates a master index of all worksheets in your workbook.

This may sound intimidating, but even beginners should be able to follow along…

This project takes about 10 minutes to implement and requires only fundamental VBA concepts, making it an ideal starting point for those new to Excel automation.

Technical Setup

You'll need Excel 2010 or later with the Developer tab enabled and basic familiarity with the VBA editor (I'll show you how to access it).

The macro requires only standard Excel objects and properties, with no external references or special permissions needed.

If you don’t see your Developer tab in the ribbon, you can enable it by selecting File - Options - Customize Ribbon. Then check the Developer tab under the Main Tabs section.

Code Implementation

Our macro will create a new worksheet named "Directory" at the beginning of your workbook to serve as the master index.

The code will loop through all worksheets in your workbook, collecting their names and placing them in an organized list on the directory sheet.

For added functionality, we'll include hyperlinks to each worksheet, allowing for single-click navigation throughout your workbook.

Practical Applications

The longer you use Excel, the bigger datasets you will encounter. This directory tool proves invaluable for financial controllers managing monthly reports across multiple departments or inventory managers tracking different product categories in separate worksheets.

The automated nature of the tool means your directory stays current even as you add or remove worksheets, and you can easily customize the format to include additional information like sheet descriptions or last-modified dates.

How to Refresh the Data

Let’s add a button on the Directory tab that refreshes our data. If we’ve added, deleted or renamed sheets, this will update our directory:

1. Go to Developer tab > Insert

2. Under "Form Controls" click the Button icon (first option)

3. Draw the button on your sheet

4. When prompted, select "CreateSheetDirectory" macro

5. Right-click the button to edit the text to say "Update Directory"

The Full VBA Code

I’ll walkthrough what’s happening in each section of the VBA code.

The first section declares our variables and checks if a Directory sheet already exists in the workbook. The loop examines each worksheet until it finds one named "Directory" or completes the search:

Sub CreateSheetDirectory()
    Dim ws As Worksheet
    Dim directorySheet As Worksheet
    Dim row As Long
    Dim exists As Boolean
    
    'Check if Directory sheet exists
    exists = False
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "Directory" Then
            exists = True
            Set directorySheet = ws
            Exit For
        End If
    Next ws

If no Directory sheet exists, this creates a new one at the beginning of the workbook. Then it clears any existing content to ensure we're starting fresh.

 'Create Directory sheet if it doesn't exist
    If Not exists Then
        Set directorySheet = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
        directorySheet.Name = "Directory"
    End If
    
    'Clear existing content
    directorySheet.Cells.Clear

This creates the header row with bold formatting, establishing the structure for our directory:

'Add header
    With directorySheet
        .Range("A1").Value = "Sheet Name"
        .Range("B1").Value = "Go To Sheet"
        .Range("A1:B1").Font.Bold = True
    End With

This loop creates our directory entries, adding each sheet name and a corresponding hyperlink. It skips the Directory sheet itself and increments the row counter for each entry:

'Add sheet names and hyperlinks
    row = 2
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Directory" Then
            With directorySheet
                .Cells(row, 1).Value = ws.Name
                .Hyperlinks.Add Anchor:=.Cells(row, 2), _
                    Address:="", _
                    SubAddress:="'" & ws.Name & "'!A1", _
                    TextToDisplay:="Click to Go"
            End With
            row = row + 1
        End If
    Next ws

This section handles the visual formatting, adding borders, adjusting column widths, and applying a background color to the header row:

'Format the directory
    With directorySheet
        .Columns("A:B").AutoFit
        .Range("A1:B" & row - 1).Borders.LineStyle = xlContinuous
        .Range("A1:B1").Interior.ColorIndex = 15
    End With

Finally, we make the Directory sheet active and display a message to confirm completion. This provides visual feedback that the macro has finished successfully:

'Activate Directory sheet
    directorySheet.Activate
    
    MsgBox "Sheet Directory has been created!", vbInformation
End Sub

Put it all together, and here is the complete VBA code for the macro:

Sub CreateSheetDirectory()
    Dim ws As Worksheet
    Dim directorySheet As Worksheet
    Dim row As Long
    Dim exists As Boolean
    
    'Check if Directory sheet exists
    exists = False
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "Directory" Then
            exists = True
            Set directorySheet = ws
            Exit For
        End If
    Next ws
    
    'Create Directory sheet if it doesn't exist
    If Not exists Then
        Set directorySheet = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
        directorySheet.Name = "Directory"
    End If
    
    'Clear existing content
    directorySheet.Cells.Clear
    
    'Add header
    With directorySheet
        .Range("A1").Value = "Sheet Name"
        .Range("B1").Value = "Go To Sheet"
        .Range("A1:B1").Font.Bold = True
    End With
    
    'Add sheet names and hyperlinks
    row = 2
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Directory" Then
            With directorySheet
                .Cells(row, 1).Value = ws.Name
                .Hyperlinks.Add Anchor:=.Cells(row, 2), _
                    Address:="", _
                    SubAddress:="'" & ws.Name & "'!A1", _
                    TextToDisplay:="Click to Go"
            End With
            row = row + 1
        End If
    Next ws
    
    'Format the directory
    With directorySheet
        .Columns("A:B").AutoFit
        .Range("A1:B" & row - 1).Borders.LineStyle = xlContinuous
        .Range("A1:B1").Interior.ColorIndex = 15
    End With
    
    'Activate Directory sheet
    directorySheet.Activate
    
    MsgBox "Sheet Directory has been created!", vbInformation
End Sub

ElevenLabs is an innovative tool that produces incredibly realistic AI voices.
Check it out here.

NEXT STEPS

Whenever you’re ready, here’s how I can help:

1. Business tech stack (FREE)
   My recommendations for software and tools I use to create content and run my online business.

2. Sponsor Got Sheet
   Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.

3. Personal budget tool
   As a Got Sheet subscriber, I want you to have a 75% discount on the personal budget I built 10+ years ago and am still using to this day.

4. YouTube

   If you aren’t subscribed yet, come on over to my YouTube channel where I make all my spreadsheet, coding and productivity tutorials

HAPPY SPREADSHEETING!

Enjoyed this issue?
Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.