ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Science & Programming»
  • Programming Languages

VBA: Add Worksheet and Change Worksheet Order in Excel

Updated on September 17, 2012

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.

Sheets(worksheetnameasstring).select

i.e.

Sheets("Sheet1").select

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.

i.e.

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

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:

Sheets(sheetnameasstring).Name=sheetnameasstring

i.e.

The code below would rename "Sheet1" "Test"

Sheets("Sheet1").Name="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"

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

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

Ready to Proceed?

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

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.