Accessing Excel sheet without mentioning its name

Question

How can I refer Excel sheet using VBA which has only one sheet without mentioning the sheet name in my program? Actually the sheet name might change next time with new data and it is creating a problem in my automation process.

Answer 1

To add something to @Mr.E. answer, note that each sheet as 2 names:
- the tab name, visible by the users, appearing within parenthesis in the Project Explorer
- the vba sheet name, appearing first in Project Explorer

So Sheets("MyList").Range(myRange) could be the exact equivalent of
Sheet1.Range(myRange) and, of course to Worksheets(1).Range(myRange) if you have only 1 sheet.

Answer 2

If your macro is running on the same file, you can simply refer to it using the global ActiveWorksheet object.

If you are loading it from somewhere else, you will be probably doing something like this:

Dim objWorkbook as Workbook
Dim objWorksheet as Worksheet

Set objWorkbook = Workbooks.Open("my-file.xlsx")
Set objWorksheet = objWorkbook.Worksheets(1)