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
- Referencing a Worksheet in VBA
- Adding a Worksheet
- Deleting a Worksheet
- Renaming the Worksheets
- Adding Multiple Sheets
- Assigning Worksheet Object to a Variable
- Hide Worksheets Using VBA
- To unhide the sheets
- Hide Sheets Based on the Text in it
- Sorting the Worksheets in an Alphabetical Order
- Creating a Table of Contents of All Worksheets with Hyperlinks
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.
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,
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.
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.
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:
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
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:
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.
(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,
Sub SheetActivate() Workbooks("Examples.xlsx").Worksheets("Sheet1").Activate End Sub
Adding a Worksheet
When you need to add a worksheet
Sub AddSheet() Worksheets.Add End Sub
Deleting a Worksheet
When you want to delete a worksheet:
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:
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,
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
Adding Multiple Sheets
When you need to add multiple sheets
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.
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.
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.
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
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.
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.
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:
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.