I have noticed that a lot of the procedures in this forum contain references to Worksheets like Sheets("Sheet1"
.Activate.
This should never have to be done! You can change the Worksheet Reference Name or "(Name)" with parenthases in the VBE. The Worksheet Reference Name differs from the Worksheet Name in that the Worksheet Name is the Name that you see on the Worksheet Tab in Excel and the Worksheet Reference Name is the Name that can be used in procedures to refer to a specific Worksheet in a Workbook.
Some of the procedures I have seen even have the Worksheets set as a variable like Set mySheet = Worksheets("Sheet1"
to allow the programmer to refer to the Worksheet later in his/her code as mySheet.
This is unnecessary, because when you change the Worksheet Reference Name in the VBE it is pretty much the same as setting a Global variable for that sheet.
To change the Worksheet Reference Name, click on the Worksheet object in the VBE and go to the Properties for that sheet. Change the "(Name)" (not "Name"
to something like sBudget (or even mySheet). Now you can refer to that Worksheet in any module in your application as sBudget (or mySheet).
This can be, and should be done for all of the Worksheets in a workbook. for example:
Worksheets("Annual Budget"
.Activate should be sBudget.Activate.
The same can be done for Workbooks by changing ThisWorkbook to wbBudget.
After that you will never have to type Workbooks("Budget Analysis NA.xls"
.Worksheets("Budget"
.Activate again. Now you will only have to type wbBudgetNA.sBudget.Activate!!! ![[thumbsup2] [thumbsup2] [thumbsup2]](/data/assets/smilies/thumbsup2.gif)
Now, Have fun going through all of your previous applications and changing your Worksheet references! ;-)
I hope this helps someone!
Peace!![[peace] [peace] [peace]](/data/assets/smilies/peace.gif)
Mike
Never say Never!!!
Nothing is impossible!!!
This should never have to be done! You can change the Worksheet Reference Name or "(Name)" with parenthases in the VBE. The Worksheet Reference Name differs from the Worksheet Name in that the Worksheet Name is the Name that you see on the Worksheet Tab in Excel and the Worksheet Reference Name is the Name that can be used in procedures to refer to a specific Worksheet in a Workbook.
Some of the procedures I have seen even have the Worksheets set as a variable like Set mySheet = Worksheets("Sheet1"
This is unnecessary, because when you change the Worksheet Reference Name in the VBE it is pretty much the same as setting a Global variable for that sheet.
To change the Worksheet Reference Name, click on the Worksheet object in the VBE and go to the Properties for that sheet. Change the "(Name)" (not "Name"
This can be, and should be done for all of the Worksheets in a workbook. for example:
Worksheets("Annual Budget"
The same can be done for Workbooks by changing ThisWorkbook to wbBudget.
After that you will never have to type Workbooks("Budget Analysis NA.xls"
![[thumbsup2] [thumbsup2] [thumbsup2]](/data/assets/smilies/thumbsup2.gif)
Now, Have fun going through all of your previous applications and changing your Worksheet references! ;-)
I hope this helps someone!
Peace!
![[peace] [peace] [peace]](/data/assets/smilies/peace.gif)
Mike
Never say Never!!!
Nothing is impossible!!!