Sheets & WorkSheets in Excel VBA

In this article you will be learning about sheets vs worksheets in excel VBA and how to use of these functions when manipulating spreadsheets.

Difference between Worksheets and Sheets in VBA

In VBA, you have two collections that can be a bit confusing at times. In a workbook, you can have worksheets and as well as chart sheets.

In Excel VBA:

• The ‘Worksheets’ collection would refer to the collection of all the worksheet objects in a workbook.
• The ‘Sheets’ collection would refer to all the worksheets as well as chart sheets in the workbook.

To run the VBA code in Excel, perform the following first

•   Under the developer tab, click visual basic
•   Click the insert option and choose a module
•   Enter your codes and click run.

Sheets

Now we know, ‘sheets’ is the collection of worksheets and chart sheets.

Looping through each Object in the Sheets collection

To loop through every sheet,

Code:

Sub UsingObject()

Dim obj As Object

For Each obj In ActiveWorkbook.Sheets

    MsgBox obj.Name

Next obj

End Sub

Looping through every Sheet in the Sheets collection

We can also count the sheets, then loop using a For loop.

Code

Sub UsingCount()

Dim i As Integer

For i = 1 To Sheets.Count

    MsgBox Sheets(i).Name

Next i

End Sub

This method of looping by counting the objects will work equally well with Charts and Worksheets.

Worksheets

When you have to work with worksheets only, use the ‘Worksheets’ collection, and when you have to refer to all sheets, then use the ‘Sheets’ collection.

Let’s see worksheets in detail.

Referencing a Worksheet in VBA

You can refer a worksheet in the following methods.

Using the Worksheet Name

This is the easiest way to refer to a worksheet.

When you are working with a workbook with three worksheets namely Sheet 1, Sheet 2, Sheet 3 (which is common in any excel file) and you want to activate Sheet 3.

Use the following code:

Code:

Sub ActivateSheet()

Worksheets("Sheet3").Activate

End Sub

You can also use the sheets collection method to activate the sheets, as we are using the name of the sheet as the key point.

Use this code

Code:

Sub ActivateSheet()

Sheets("Sheet3").Activate

End Sub

Using the Index Number

The difficult part of using the name of the sheet to refer them is you need to know the exact name of the sheet or the program doesn’t work.

In this case, you can use the index number of the worksheets. The indexing starts from 1 in the collection of sheets.

Use this code to activate Sheet3:

Code

Sub ActivateSheet()

Worksheets(3).Activate

End Sub

Important: A chart sheet is not a part of the worksheets collection.

This is because when we use the index numbers in the Worksheet collection, it will only refer to the worksheets in the workbook.

Note: Indexing goes from left to right. So if you shift Sheet3 to the left of Sheet2, then Worksheets (2) would refer to Sheet3.

Using the Worksheet Code Name

You can use the code name of the worksheet to refer to a worksheet.  This code name can be assigned in the VB Editor and it won’t change when you change the name of the worksheet.

To give your worksheet a code name, follow these steps:

• Under the Developer tab, click the Visual Basic option.
•  This will open the VB Editor.
• Now, Click the View option in the menu and click on Project Window.
• Click on the sheet name in the project explorer that you want to rename.
• In the Properties pane, change the name in the field in front of (Name).

Note: Don’t include spaces in the name.

This would change the name of your Worksheet in the VBA, i.e., the code name. Therefore, when you change the worksheet name it doesn’t affect the code in your VBA.

Now, you can use either the Worksheets collection to refer to the worksheet or use the codename.

The following code uses both worksheet collection method and name of the sheet method.

Code

Worksheets("Sheet3").Activate

SH3.Activate

(I have code named my sheet as SH3)

Referring to a Worksheet in a Different Workbook

If you need to access a worksheet in a different workbook,

Code

Sub SheetActivate()

Workbooks("Examples.xlsx").Worksheets("Sheet1").Activate

End Sub

Adding a Worksheet

When you need to add a worksheet

Code

Sub AddSheet()

Worksheets.Add

End Sub

Deleting a Worksheet

When you want to delete a worksheet:

Code

Sub DeleteSheet()

ActiveSheet.Delete

End Sub

Click ok on the warning prompt. The worksheet gets deleted.

To avoid the warning prompt, use the below code:

Code

Sub DeleteSheet()

Application.DisplayAlerts = False

ActiveSheet.Delete

Application.DisplayAlerts = True

End Sub

Note: You can’t undo this delete option. So be sure.

Delete a specific worksheet.

If you want to delete a specific sheet,

Code

Sub DeleteSheet()

Worksheets("Sheet3").Delete

End Sub

You can also use the code name of the sheet to delete it.

Sub DeleteSheet()

SH3.Delete

End Sub

Renaming the Worksheets

When you want to rename the sheets using VBA code:

Sub RenameSheet()

Worksheets("Sheet1").Name = "Naming sheet"

End Sub

Code

Adding Multiple Sheets

When you need to add multiple sheets

Code

Sub RenameSheet()

Dim Countsheets As Integer

Countsheets = Worksheets.Count

For i = 1 To 4

Worksheets.Add after:=Worksheets(Countsheets + i – 1)

Worksheets(Countsheets + i).Name = "Multiple Sheets 1" & i

Next i

End Sub

Assigning Worksheet Object to a Variable

You can assign a worksheet to an object variable, and then use the variable instead of the worksheet references.

Code

Sub RenameSheet()

Dim Ws As Worksheet

For Each Ws In Worksheets

Ws.Name = "Assigning Variable " & Ws.Name

Next Ws

End Sub

Hide Worksheets Using VBA

You can hide and unhide worksheets using VBA. Normally when a worksheet is hidden, you can easily unhide the worksheet by right-clicking on any sheet tab.

But if you don’t want to unhide the worksheet in this method, you can do this using VBA.

The code below would hide all the worksheets in the workbook (except the active sheet), such that you cannot unhide it by right-clicking on the sheet name.

Code

Sub HideAllExcetActiveSheet()

Dim Ws As Worksheet

For Each Ws In ThisWorkbook.Worksheets

If Ws.Name <> ActiveSheet.Name Then Ws.Visible = xlSheetVeryHidden

Next Ws

End Sub

If you want to hide sheets that can be unhidden easily, use the below code.

Code

Sub HideAllExceptActiveSheet()

Dim Ws As Worksheet

For Each Ws In ThisWorkbook.Worksheets

If Ws.Name <> ActiveSheet.Name Then Ws.Visible = xlSheetHidden

Next Ws

End Sub

To unhide the sheets

Code:

Sub UnhideAllWoksheets()

Dim Ws As Worksheet

For Each Ws In ThisWorkbook.Worksheets

Ws.Visible = xlSheetVisible

Next Ws

End Sub

Hide Sheets Based on the Text in it

You can hide sheets based on the text in it. You can do this using the VBA INSTR function.

The below code would hide all the sheets except the ones with the text 2020 in it.

Code:

Sub HideWithMatchingText()

Dim Ws As Worksheet

For Each Ws In Worksheets

If InStr(1, Ws.Name, "2020", vbBinaryCompare) = 0 Then

Ws.Visible = xlSheetHidden

End If

Next Ws

End Sub

Sorting the Worksheets in an Alphabetical Order

Using VBA, you can quickly sort the worksheets based on their names.

Use the below code to quickly sort sheets in an ascending order.

Code

Sub SortSheetsTabName()

Application.ScreenUpdating = False

Dim ShCount As Integer, i As Integer, j As Integer

ShCount = Sheets.Count

For i = 1 To ShCount – 1

For j = i + 1 To ShCount

If Sheets(j).Name < Sheets(i).Name Then

Sheets(j).Move before:=Sheets(i)

End If

Next j

Next i

Application.ScreenUpdating = True

End Sub

Creating a Table of Contents of All Worksheets with Hyperlinks

To create a table of contents of all worksheets:

Code

Sub AddIndexSheet()

Worksheets.Add

ActiveSheet.Name = "Index"

For i = 2 To Worksheets.Count

ActiveSheet.Hyperlinks.Add Anchor:=Cells(i – 1, 1), _

Address:="", SubAddress:=Worksheets(i).Name & "!A1", _

TextToDisplay:=Worksheets(i).Name

Next i

End Sub

The above code inserts a new worksheet and names it Index.

It then loops through all the worksheets and creates a hyperlink for all the worksheets in the Index sheet.