VBA: Add Worksheet and Change Worksheet Order in Excel
Think of a worksheet as a unique layer of cells within a Workbook. By default each workbook has three worksheets.
Worksheets can be added or delete as needed.
Moving Between Worksheets in Excel
A user can move between worksheets by clicking the tabs located near the bottom of the screen.
The sheet currently selected is none as the "Active Sheet". From the excel user interface changes can only be made on the active sheet.
Note: By default there are three tabs name: "Sheet1", "Sheet2", and "Sheet3"
Changing the Name of a Worksheet in Excel
To change the name of a worksheet right click on the tab and select "Rename"
Changing the Order of Worksheets in Excel
Hold the left mouse button and drag the tab to the desired location.
Adding a Worksheet in Excel
Right click on the spreadsheet to the right of where you would like to insert your sheet and select insert
The example below would insert a spreadsheet the left of "Sheet3"
Selecting Worksheets from VBA
To select a worksheet from VBA use the following code.
Note: It is very rare to need to select a sheet from VBA.
Referencing a Ranges Sheet in VBA
When working with ranges in VBA get in the habit of specifying the sheet on which the range resides.
This will allow your code to be executed successfully from any sheet in your workbook.
Changing a Worksheet's Name from VBA
To change a worksheets name use the following code:
The code below would rename "Sheet1" "Test"
Changing the Order of Worksheets from VBA
In visual basic you can change the order of worksheets by specifying the name of the sheet before or after the location you would like it moved. For Example both of the following lines of code would move "Sheet3" between "Sheet1" and "Sheet2"
Ready to Proceed?
In the next module we will learn to open workbooks in excel and visual basic.