Note: The index number goes from left to right.
![excel vba excel vba](https://cdn.educba.com/academy/wp-content/uploads/2019/10/VBA-Editor-in-Excel.png)
#Excel vba how to
You can count the number of worksheets and then loop through these using this count (we will see how to do this later in this tutorial). This technique of using index number is useful when you want to loop through all the worksheets in a workbook. On the contrary, if you’re using Sheets, Sheets(1) would refer to Sheets1, Sheets(2) would refer to Sheet2, Sheets(3) would refer to Chart1 and Sheets(4) would refer to Sheet3. So when we use the index numbers in the Worksheets collection, it will only refer to the worksheets in the workbook (and ignore the chart sheets). This happens because a chart sheet is not a part of the worksheets collection. If you’re wondering why it selected Sheet3, as it’s clearly the fourth object. Now, what happens when you use 3 as the index number? This would refer to the second object in the collection of the worksheets. Note that we have used index number 2 in Worksheets(2).
#Excel vba code
The below code would activate Sheet2: Sub ActivateSheet() Suppose you have the following sheets in a workbook: In this case, you can use the index number of the worksheets. While using the sheet name is an easy way to refer to a worksheet, sometimes, you may not know the exact name of the worksheet.įor example, if you’re using a VBA code to add a new worksheet to the workbook, and you don’t know how many worksheets are already there, you would not know the name of the new worksheet. So the below code would also do that same thing. Since we are using the exact sheet name, you can also use the Sheets collection here. The above code asks VBA to refer to Sheet2 in the Worksheets collection and activate it. You can do that using the following code: The easiest way to refer to a worksheet is to use its name.įor example, suppose you have a workbook with three worksheets – Sheet 1, Sheet 2, Sheet 3.Īnd you want to activate Sheet 2. Understanding how to refer to worksheets would help you write better code, especially when you’re using loops in your VBA code. There are many different ways you can use to refer to a worksheet in VBA. In this tutorial, I will be using the ‘Worksheets’ collection only. So if you have to refer to worksheets only, use the ‘Worksheets’ collection, and if you have to refer to all sheets (including chart sheets), the use the ‘Sheets’ collection. Now with this distinction, I recommend being as specific as possible when writing a VBA code. If you have a workbook that only has worksheets and no chart sheets, then ‘Worksheets’ and ‘Sheets’ collection is the same.īut when you have one or more chart sheets, the ‘Sheets’ collection would be bigger than the ‘Worksheets’ collection In the above example, it would have four elements – 3 Worksheets + 1 Chart sheet.
![excel vba excel vba](https://cdn.corporatefinanceinstitute.com/assets/excel-vba.jpg)
![excel vba excel vba](https://cdn.educba.com/academy/wp-content/uploads/2019/03/VBA-Find-in-Excel-1.png)
The ‘Worksheets’ collection would refer to the collection of all the worksheet objects in a workbook.The example below has three worksheets and one chart sheet. In a workbook, you can have worksheets and as well as chart sheets. In VBA, you have two collections that can be a bit confusing at times. Creating a Table of Contents of All Worksheets (with Hyperlinks)ĭifference between Worksheets and Sheets in VBA.Protect/Unprotect All the Sheets at One Go.Sorting the Worksheets in an Alphabetical Order.Hide Worksheets Using VBA (Hidden + Very Hidden).Assigning Worksheet Object to a Variable.Referring to a Worksheet in a Different Workbook.Difference between Worksheets and Sheets in VBA.