Sheet/Worksheet CodeNames
It is generally important when writing code to refer to a sheet for the code to work on.
Range("A1".Value="Test" Or Cells(1,1).Value="Test"
The code above will only work on the ActiveSheet. To work with a specific sheet then the ode must identify the sheet. This can be done in one of these ways:
Sheet Index NameThis is the position of the Sheet in the WorkBook, Excel determines the Index by numbering the Sheets from the left. So, to refer to the second sheet use
Sheets(2).Cells(1,1).Value="Test"This is fine unil someone moves the sheets around or adds sheets before the sheet you used, then the code will not have the expected results, it will write to the wrong sheet.
Sheet Tab Name: This is the name that is manually entered into the Tabs of the WorkBook. By default Sheet1,Sheet2 etc. You should know that these can be altered by right clicking on the tab & choosing Rename. To use this method in your code
Sheets("Main").Cells(1,1).Value="Test"
However, this method too has a drawback, the user can change the sheet name as easily as you. If this happens the code will not work.
Sheet Code Name: Each Sheet in a Workbook is given a unique CodeName that does not change even when that sheet is moved, renamed or other sheets are added. Each sheets CodeName can only be seen by going into the Visual Basic Editor (Tools->Macro->Visual Basic Editor or Alt+F11) and then displaying the Project Explorer (View->Project Explorer or Ctl+R)
In the screenshot the Sheet named Main has a code name of Sheet2,that is the name outside the parentheses (brackets). This name cannot be changed by the user as the above ways can. So this is the generally accepted method used by experienced "coders".
So,
Sheet2.Cells(1,1).Value="Test"
This code will now refer to the sheet named "Main".
However, why not take it one step further? The Sheet Code Name can be changed in the VB Editor whilst designing the workbook like any other object such as a UserForm. You select the Name Property in the Properties Window & change it to something meaningful to it's use, like in this case MAIN or INVOICE.
Now when coding the name should be easy to remember and you can use [CODE]INVOICE.Cells(1,1).Value="Test"[/CODE] This is muchsimpler & fairly foolproof. However, remember if the sheet that you are referencing is in another workbook yo must include that WorkBook's name in the code.