nsafans.blogg.se

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. The ‘Sheets’ collection would refer to all the worksheets as well as chart sheets in the workbook.In the above example, the Worksheets collection would consist of three worksheets. 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.