VBA Blogs: Referring to Sheets
10 May 2019
Welcome back to the VBA blog! Last week, we looked at how referencing sheets can be fraught with peril in the event that sheet names change, and we showed how you can use the VBA sheet name to avoid that issue.
This week, we’re going to look at working with sheets from left to right.
The VBA code “Sheets” essentially refers to the entire collection of sheets in the workbook. Instead of referring to Sheets(“Sheet1”), which will look for a sheet that is labelled “Sheet1”, we can try using Sheets(1) instead.
Sheets(1) will essentially refer to the first sheet in the workbook. So, code like the following will result in VBA activating the first sheet in the model:
Likewise, I can use any of the other Sheets properties here as well:
In this way, if you know that your data is always going to be in the 1st sheet, or the Nth sheet, you can code that logic into your VBA.
Come back next week for more VBA tips!
