If you are new to VBA then this post is a great place to start.I like to break things down into simple terms and explain them in plain English without the jargon.So far we have been using the sheet name to access the sheet.The index refers to the sheet tab position in the workbook.This collection is simply called Worksheets and is used in a very similar way to the Workbooks collection.To get access to a worksheet all you have to do is supply the name.Declaring a worksheet object is useful for making your code neater and easier to read.The next example shows code for updating ranges of cells. The second sub declares a worksheet object and the code is therefore much clearer.
If you use a worksheet method like Range and don’t mention the worksheet, it will use the active worksheet by default.
The three most important elements of VBA are the Workbook, the Worksheet and Cells.
Of all the code your write, 90% will involve one or all of them.
This makes the code easy to read and safe from the user changing the sheet name. It can only refer to worksheets in the workbook that contains the code i.e. However, we can use a simple function to find the code name of a worksheet in a different workbook.
Using the above code means that if the user changes the name of the worksheet then your code will not be affected.This post provides a complete guide to using the Excel VBA Worksheet in Excel VBA.