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.

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"

To change the name of a worksheet right click on the tab and select "Rename"

Hold the left mouse button and drag the tab to the desired location.

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"

To select a worksheet from VBA use the following code.

Note: It is very rare to need to select a sheet from VBA.

Sheets(worksheetnameasstring).select

i.e.

Sheets("Sheet1").select

When working with ranges in VBA get in the habit of specifying the sheet on which the range resides.

i.e.

Sheets("Sheet1).Range("A1:A50").ClearContents

This will allow your code to be executed successfully from any sheet in your workbook.

To change a worksheets name use the following code:

Sheets(sheetnameasstring).Name=sheetnameasstring

i.e.

The code below would rename "Sheet1" "Test"

Sheets("Sheet1").Name="Test"

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"

Sheets("Sheet3").Move after:=Sheets("Sheet1")

Sheets("Sheet3").Move before:=Sheets("Sheet2")

In the next module we will learn to open workbooks in excel and visual basic.