Excel VBA Worksheets - Refer, Activate or Select, Add & Name, Copy or Move, Hide or Display, Delete, Page Layout & View, Calculate
Related Links:
Working with Objects in Excel VBA
Excel VBA Application Object, the Default Object in Excel
Excel VBA Workbook Object, working with Workbooks in Excel
Excel VBA Range Object, Referencing Cells and Ranges
Excel VBA Custom Classes and Objects
-----------------------------------------------------------------------------------------------------
Contents:
Worksheet Object, Sheets Object, Chart Object & Chart Sheet
Reference a Worksheet object ie. a single worksheet
Activate or Select a Worksheet
-----------------------------------------------------------------------------------------------------
The Worksheet Object represents a single worksheet in a workbook. The Worksheets Object refers to a collection of all worksheets in a workbook.
The Sheets Object refers to a collection of all sheets (ie. all worksheets, chart sheets, macro sheets & dialog sheets) in a workbook. The Worksheet Object is a member of both the Worksheets collection (Worksheets object) and the Sheets collection (Sheets object). The Workbook.Worksheets Property returns a Worksheets collection (ie. a Worksheets object) which refers to all worksheets in a workbook. The Workbook.Sheets Property returns a Sheets collection (ie. a Sheets object) which refers to all sheets in a workbook. Using the code MsgBox ActiveWorkbook.Worksheets.Count will return the number of worksheets in the active workbook, and the code MsgBox ActiveWorkbook.Sheets.Count will return the number of sheets in the active workbook.
The Chart Object represents a chart (single chart) in a workbook, which can be either an embedded chart or a separate chart sheet. The Charts collection refers to a collection of all chart sheets in a workbook, and excludes all embedded charts. The ChartObject Object represents an embedded chart (single embedded chart) in a sheet. The ChartObjects Object refers to a collection of all ChartObject objects (ie. embedded charts) in a single sheet (ie. in a specified chart sheet, dialog sheet or worksheet).
A Chart Sheet contains a single chart and covers the entire worksheet. A workbook can contain 4 types of sheets - worksheet, chart sheet, macro sheet (MS Excel 4.0 Macro sheet) and a dialog sheet (MS Excel 5.0 Dialog sheet). Macro sheets (also called XLM macros) & dialog sheets (used in earlier Excel versions to create customized dialog boxes / making forms, now replaced by UserForms), are still being provided & supported in Excel 2007 only for backward compatibility with earlier versions of Microsoft Excel. A macro sheet (or a dialog sheet) is not included as a part of the Worksheets collection but is a part of the Sheets collection.
Example to illustrate types of sheet objects, as explained above:
Sub sheets1()
'Consider a workbook with 3 worksheets (named: "Sheet1", "Sheet2" & "Sheet3"), 2 embedded charts in "Sheet1", 1 chart sheet ("Chart1"), 1 macro sheet ("Macro1") & 1 dialog sheet ("Dialog1").
'-----------------------------
Dim ws As Worksheet
Dim i As Integer
'returns 6 (3 worksheets, 1 chart sheet, 1 macro sheet & 1 dialog sheet):
MsgBox ThisWorkbook.Sheets.count
'returns the names of each of the 6 sheets:
For i = 1 To ThisWorkbook.Sheets.count
MsgBox ThisWorkbook.Sheets(i).Name
Next i
'returns 3 (3 worksheets - "Sheet1", "Sheet2" & "Sheet3" ):
MsgBox ThisWorkbook.Worksheets.count
'returns the names of each of the 3 worksheets:
For Each ws In ThisWorkbook.Worksheets
MsgBox ws.Name
Next
'-----------------------------
Dim oSh As Object
'returns the names of each of the 6 sheets (3 worksheets, 1 chart sheet, 1 macro sheet & 1 dialog sheet):
For Each oSh In ThisWorkbook.Sheets
MsgBox oSh.Name
Next
'returns the names of each of the 3 worksheets ("Sheet1", "Sheet2" & "Sheet3"):
For Each oSh In ThisWorkbook.Worksheets
MsgBox oSh.Name
Next
'-----------------------------
'returns 1 - there is 1 chart object (chart sheet named "Chart1") in this workbook:
MsgBox ThisWorkbook.Charts.count
'returns 0 - there is no embedded chart (ChartObject Object) in the chart sheet:
MsgBox Sheets("Chart1").ChartObjects.count
'returns 2 - there are 2 embedded charts (ChartObject Objects) in the worksheet named "Sheet1":
MsgBox Sheets("Sheet1").ChartObjects.count
End Sub
Reference a Worksheet object ie. a single worksheet.
To reference or return a Worksheet object (single worksheet), we use properties of both the Worksheets & the Worksheet object, as shown below.
The Item Property of the Worksheets object (ie. Worksheets.Item Property) refers to a single worksheet in a collection. Syntax: WorksheetsObject.Item(Index), where Index is the worksheet name or index number. You can also omit using the 'item' word - use syntax as WorksheetsObject(WorksheetName) or WorksheetsObject(IndexNumber).
The index number starts at 1 for the first or leftmost worksheet on the workbook tab bar and increments accordingly for each worksheet moving towards the right (hidden worksheets are also included), the last (rightmost) worksheet being returned by Worksheets(Worksheets.Count). The Count property of the Worksheets object - Worksheets.Count Property - returns the number of worksheets in the collection (ie. in the workbook). Refer below examples of using this property.
You can similarly use the Sheets.Item Property to refer to a single sheet in the sheets collection. Syntax: SheetsObject.Item(Index).
The worksheet tab displays the name of the worksheet. Use the Name property of the Worksheet object (Worksheet.Name Property) to set or return the name of a worksheet. Syntax: WorksheetObject.Name. Name property has been illustrated in detail below, under the heading "Adding & Naming Worksheets".
Some codes to refer a worksheet:
Return the name of the first worksheet in the active workbook - both using & omitting the "item" word:
MsgBox ActiveWorkbook.Worksheets.Item(1).Name
MsgBox ActiveWorkbook.Worksheets(1).Name
Return the name of the first sheet in the active workbook - both using & omitting the "item" word - note that the first sheet may be different from the first worksheet:
MsgBox ActiveWorkbook.Sheets.Item(1).Name
MsgBox ActiveWorkbook.Sheets(1).Name
Return the name of the first worksheet in ThisWorkbook (where the code is running), omitting the "item" word:
MsgBox ThisWorkbook.Worksheets(1).Name
Activate the worksheet named "Sheet3" of the active workbook:
ActiveWorkbook.Worksheets("Sheet3").Activate
Activate the sheet named "Chart1" of the active workbook:
ActiveWorkbook.Sheets("Chart1").Activate
Note: using the code ActiveWorkbook.Worksheets("Chart1").Activate to activate a chart sheet will give an error.
Not specifying a workbook will refer to the Active Workbook - return the name of the second worksheet in the active workbook:
MsgBox Worksheets(2).Name
Not specifying a workbook will refer to the Active Workbook - return the name of the second sheet in the active workbook - note that the second sheet may be different from the second worksheet:
MsgBox Sheets(2).Name
Refer to the active sheet in the active workbook:
MsgBox ActiveSheet.Name
Return the name of the second worksheet in the specified workbook (ie. workbook named "ExcelVBA.xlsx"):
MsgBox Workbooks("ExcelVBA.xlsx").Worksheets(2).Name
Return the name of the first worksheet in the second workbook:
MsgBox Workbooks(2).Worksheets(1).Name
MsgBox Workbooks.Item(2).Worksheets.Item(1).Name
MsgBox Workbooks(2).Worksheets.Item(1).Name
Example - refer worksheets:
Sub worksheets1()
'consider the active workbook workbook containing 3 worksheets, namely "Sheet1", "Sheet2" & "Sheet3" in the order of left to right.
'returns the name of the first worksheet ie. "Sheet1":
MsgBox ActiveWorkbook.Worksheets(1).Name
'changes the name of the first worksheet ie. "Sheet1" to "WS3":
ActiveWorkbook.Worksheets(1).Name = "WS3"
'returns the new name of the first worksheet ie. "WS3":
MsgBox Worksheets(1).Name
'returns the name of the last worksheet ie. "Sheet3":
MsgBox ActiveWorkbook.
Worksheets(Worksheets.count).Name
'--------------------------
'returns 3, the total no of worksheets:
MsgBox ActiveWorkbook.Worksheets.Count
'returns the value -1, indicating the worksheet named "Sheet2" is visible:
MsgBox Worksheets("Sheet2").Visible
'hides "Sheet2":
Worksheets("Sheet2").Visible = False
'returns the value 0, indicating "Sheet2" is hidden:
MsgBox Worksheets("Sheet2").Visible
'returns 3, the total no of worksheets counting the hidden one also:
MsgBox Worksheets.Count
'unhides "Sheet2" and makes it visible:
Worksheets("Sheet2").Visible = True
End Sub
While referring to a range, omitting the object qualifier - worksheet object - will default to the Active Sheet viz. using the code Range("A1") will return cell A1 of the active sheet, and will be the same as using Application.Range("A1") or ActiveSheet.Range("A1").
Enter the value 10 in cell A1 of the active worksheet:
ActiveSheet.Range("A1").Value = 10
Range("A1").Value = 10
Application.Range("A1").Value = 10
The Active Object
If no Workbook or Worksheet is specified, Excel refers to the current Active Workbook or Worksheet by default. In your vba code you can also refer the current Active Workbook or Sheet as ActiveWorkbook or ActiveSheet. Both the expressions Worksheets(1).Name and ActiveWorkbook.Worksheets(1).Name will return the name of the first worksheet in the Active Workbook which also becomes the default object in this case. Similarly, both the expressions Range("A1").Value = 56 and ActiveSheet.Range("A1").Value = 56 will enter the value 56 in cell A1 of the Active Worksheet in the Active Workbook. This is a general rule that omitting reference to a Workbook or Worksheet refers to the current Active Workbook or Worksheet by default, but this rule is subject to below conditions.
Note: (i) omitting reference to a Worksheet when your vba code is entered in Sheet Modules (viz. Sheet1, Sheet2, ...) will reference the specific sheet in whose module your code is entered and NOT the Active Sheet; and (ii) omitting reference to a Workbook when your vba code is entered in the Workbook module (ThisWorkbook) will reference the workbook in which your code is entered and NOT the Active Workbook. This means: (i) omitting reference to a Worksheet will default to ActiveSheet when your vba code is entered in Standard Code Modules (Module1, Module2, …) or the Workbook module (ThisWorkbook) and NOT when your vba code is entered in Sheet Modules (viz. Sheet1, Sheet2, ...) or UserForms or any Class modules you create; and (ii) omitting reference to a Workbook will default to ActiveWorkbook when your vba code is entered in Standard Code Modules (Module1, Module2, …) or in the Sheet Modules (viz. Sheet1, Sheet2, ...) and NOT when your vba code is entered in the Workbook module (ThisWorkbook).
Code Name and Sheet Name
In the Project Explorer Window in VBE, the Objects folder is always present and contains: a sheet object for each existing worksheet, and a ThisWorkbook object. Each sheet object has as its first name appearing outside the parentheses which is the sheet code name, and the second name which appears after the code name and within the parentheses is the sheet tab name which appears on the tab of the Excel worksheet. The code name of the selected worksheet appears to the right of (Name) in the Properties Window while the sheet name appears to the right of Name when you scroll down in the Properties Window. By default, when a worksheet is added, the code name and sheet name are the same - default code names and default sheet names start from "Sheet1", "Sheet2", … in that order from left to right. You can change both the code name and the sheet name - while the sheet name can be changed in the Properties window (in VBE) or in the sheet tab or from the ribbon (Home tab - Cells section - click Format) or by vba code, but the code name can be changed only in the Properties window and not programmatically with code. Both code name and sheet name can be used while writing code - using code name will safeguard and not affect the vba code if users change the sheet name. We show below how to use these names in vba code.
Refer worksheets - Worksheets("Sheet1") vs Sheet1:
The following code inserts the text "hello" in cell A1 of the worksheet whose code name is Sheet1:
Sheet1.Range("A1").Value = "hello"
The following code inserts the text "hello" in cell A1 of the worksheet whose sheet name (ie. name on the worksheet tab) is Sheet1:
Worksheets("Sheet1").Range("A1").Value = "hello"
The following code inserts the text "hello" in cell A1 of the first worksheet (counting worksheet tabs left to right):
Worksheets(1).Range("A1").Value = "hello"
Activate or Select a Worksheet
The active worksheet is the worksheet which you are currently viewing or working in. The Workbook.ActiveSheet Property returns the currently Active Sheet in a specific workbook or window (Syntax: WorkbookObject.ActiveSheet). For details, refer section on Excel VBA - Workbooks. Use the Worksheet.Activate Method to activate a sheet. Syntax: WorksheetObject.Activate. Note that you can select multiple worksheets, but activate only a single worksheet. Use the Worksheet.Select Method to select a worksheet. Syntax: WorksheetObject.Select(Replace). Replace is an optional argument, used only while using the Select method for selecting sheets (Select method is also used to select cells). Specifying True for this argument will replace the previous selection whereas False will include the previous selection (thus extending the current selection to include the previously selected sheets) and omitting the argument will default to True. Use the Worksheets.Select Method (Select method of the Worksheets object) to select all worksheets in a workbook. Syntax: WorksheetsObject.Select(Replace).
Wherein multiple, but not all, worksheets in a workbook are currently selected: Activating a sheet which is one out of multiple sheets that are currently selected, will not deselect the selected sheets; If the activated sheet is not one of the currently selected sheets, the activated sheet will become the only currently selected sheet. Wherein all worksheets in a workbook are currently selected: When all worksheets in a workbook are currently selected, activating a worksheet other than the currently activated one, makes the new activated worksheet as the only selected sheet. See the following 3 examples:
Example 1 - select single or multiple worksheets:
Sub Worksheets_Select()
'select single or multiple worksheets - consider a workbook containing 3 worksheets, namely "Sheet1", "Sheet2" & "Sheet3" in the order of left to right.
'selects and activates the second worksheet ("Sheet2") - the previous selection is replaced so this is the only selected worksheet:
ActiveWorkbook.Worksheets(2).Select
'returns the active worksheet ("Sheet2"):
MsgBox ActiveWorkbook.ActiveSheet.Name
'select multiple worksheets in the active workbook, using an array of worksheet names - the first worksheet in the array ("Sheet3") becomes the active worksheet:
ActiveWorkbook.Sheets(Array("Sheet3", "Sheet1")).Select
'returns the active worksheet ("Sheet3"):
MsgBox ActiveWorkbook.ActiveSheet.Name
'selects and activates the second worksheet ("Sheet2") - the previous selection is replaced so this is the only selected worksheet:
ActiveWorkbook.Worksheets("Sheet2").Select
'returns the active worksheet ("Sheet2"):
MsgBox ActiveWorkbook.ActiveSheet.Name
'select multiple worksheets - all worksheets except last (this selects the worksheets "Sheet1" & "Sheet2"):
Dim i As Integer
For i = 1 To ThisWorkbook.Worksheets.count - 1
ActiveWorkbook.Worksheets(i).Select (False)
Next i
'returns the active worksheet ("Sheet2") - this was the active worksheet before the For...Next loop:
MsgBox ActiveWorkbook.ActiveSheet.Name
End Sub
Example 2 - select & activate worksheets:
Sub Worksheets_SelectActivate()
'select & activate worksheets - consider a workbook containing 4 worksheets, namely "Sheet1", "Sheet2", "Sheet3" & "Sheet4" in the order of left to right.
'selects and activates the first worksheet ("Sheet1") - the previous selection is replaced so this is the only selected worksheet:
ActiveWorkbook.Worksheets(1).Select
'returns the active worksheet ("Sheet1"):
MsgBox ActiveWorkbook.ActiveSheet.Name
'selects the second worksheet ("Sheet2"), without activating it and without deselecting previous selection:
ActiveWorkbook.Worksheets(2).Select (False)
'returns the active worksheet ("Sheet1"):
MsgBox ActiveWorkbook.ActiveSheet.Name
'selects the third worksheet ("Sheet3"), without activating it and without deselecting previous selection:
ActiveWorkbook.Worksheets(3).Select (False)
'returns the active worksheet ("Sheet1"):
MsgBox ActiveWorkbook.ActiveSheet.Name
'activates the third worksheet ("Sheet3"), without deselecting previous selection because one of the selected worksheets is activated:
ActiveWorkbook.Worksheets(3).activate
'returns the active worksheet ("Sheet3"):
MsgBox ActiveWorkbook.ActiveSheet.Name
'at this stage, 3 worksheets ("Sheet1", "Sheet2" & "Sheet3") are currently selected:
Dim ws As Worksheet
For Each ws In ActiveWindow.SelectedSheets
MsgBox ws.Name
Next
'activates and selects the fourth worksheet ("Sheet4"), which is the only selected worksheet now, deselecting previous selection because the activated sheet is NOT one of the previously selected worksheets:
ActiveWorkbook.Worksheets(4).activate
'returns the active worksheet ("Sheet4"):
MsgBox ActiveWorkbook.ActiveSheet.Name
End Sub
Example 3 - selecting all worksheets:
Sub Worksheets_SelectAll()
'selecting all worksheets - consider a workbook containing 3 worksheets, namely "Sheet1", "Sheet2" & "Sheet3" in the order of left to right.
'select all worksheets in the active workbook, using the Worksheets.Select Method:
ActiveWorkbook.Worksheets.Select
'the first worksheet becomes the active worksheet in this case - returns "Sheet1":
MsgBox ActiveWorkbook.ActiveSheet.Name
'select all worksheets in the active workbook, using an array of worksheet names:
ActiveWorkbook.Sheets(Array("Sheet2", "Sheet3", "Sheet1")).Select
'the first worksheet in the array becomes the active worksheet - returns "Sheet2":
MsgBox ActiveWorkbook.ActiveSheet.Name
'this activates and selects the third worksheet ("Sheet3"), deselecting all other worksheets:
ActiveWorkbook.Worksheets(3).activate
'returns the active worksheet ("Sheet3"):
MsgBox ActiveWorkbook.ActiveSheet.Name
'alternate way to select all worksheets in the active workbook - Worksheet.Select Method:
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible Then ws.Select (False)
Next
'the active worksheet remains the same as before the For...Next loop - "Sheet3":
MsgBox ActiveWorkbook.ActiveSheet.Name
End Sub
Window.SelectedSheets Property
Use the Window.SelectedSheets Property to determine all the selected sheets in a specified window. Syntax: WindowObject.SelectedSheets. Windows(1) refers to the active window always. Refer above example (Example 2) for use of this property. A single window (Window Object) is a member of the Windows Collection. The Windows collection contains all windows in the Excel Application (Windows collection for the Application object) or contains all windows in the specified Workbook (Windows collection for Workbook object).
Use the Sheets.Add Method to create or add a new worksheet (in a specified workbook), chart sheet, macro sheet or dialog sheet, which also becomes the active sheet. Syntax: SheetsObject.Add(Before, After, Count, Type). The default sheet names start from "Sheet1", "Sheet2", … in that order from left to right. All arguments are optional to specify. Use the Before argument to specify the sheet before which you want to add the new sheet(s). Use the After argument to specify the sheet after which you want to add the new sheet(s). The Count argument specifies the number of sheets you want to add, default being one. Use the Type argument to specify a constant or value (XlSheetType constant) indicating the type of sheet to add. XlSheetType constants: xlWorksheet (worskheet, value -4167), xlChart (chart, value -4109), xlExcel4MacroSheet (Excel version 4 macro sheet, value 3), xlExcel4IntlMacroSheet (Excel version 4 international macro sheet, value 4) or xlDialogSheet (dialog sheet, value -4116).
Examples of adding a sheet(s):
Using the Add Method without specifying any argument - adds a new worksheet before the Active Worksheet because both the Before & After arguments are omitted (note that the default value of Count argument is 1):
ActiveWorkbook.Worksheets.Add
Using the Add Method specifying one named argument of After - adds a new worksheet after the Worksheet named "Sheet2" (note that the default value of Count argument is 1):
ActiveWorkbook.Worksheets.Add After:=Worksheets("Sheet2")
Using the Add Method specifying two named arguments of After & Count - adds 3 new worksheets after the Worksheet named "Sheet2":
ActiveWorkbook.Worksheets.Add After:=Worksheets("Sheet2"), Count:=3
Using the Add Method specifying two positional arguments of After & Count - adds 2 new worksheets after the Worksheet named "Sheet2":
ActiveWorkbook.Worksheets.Add , Worksheets("Sheet2"), 2
Using the Add Method specifying two positional arguments of Before & Count - adds 2 new worksheets before the Worksheet named "Sheet2":
ActiveWorkbook.Worksheets.Add Worksheets("Sheet2"), , 2
Default worksheet names on creating a new workbook:
We have in the section on Excel VBA - Workbooks , explained in detail how to create a new workbook using the Workbooks.Add Method (Add Method of the Workbooks object). Unless you specify an excel file as the template for the new workbook, the Add method will create a new workbook with the Excel default of three blank sheets, wherein the default number of sheets can be changed / set by using the Application.SheetsInNewWorkbook Property. The three blank sheets are by default named "Sheet1", "Sheet2" & "Sheet3", in that order from left to right.
The worksheet tab displays the name of the worksheet. Use the Name property of the Worksheet object (Worksheet.Name Property) to set or return the name of a worksheet. Syntax: WorksheetObject.Name.
Some examples of using the Name property:
Return the name of the first worksheet in the active workbook:
MsgBox ActiveWorkbook.Worksheets(1).Name
Activate the worksheet named "Sheet1" of the active workbook:
ActiveWorkbook.Worksheets("Sheet1").Activate
Change the name of the worksheet named "Sheet1" to "Excel VBA", in the active workbook - note that changing a worksheet name does not make it the active worksheet:
ActiveWorkbook.Worksheets("Sheet1").Name = "Excel VBA"
Using the Add Method specifying one named argument of Before - adds a new worksheet before the Worksheet named "Sheet2", and using the Name property, names the new worksheet "NewSheet":
ActiveWorkbook.Worksheets.
Add(Before:=Worksheets("Sheet2")).Name = "NewSheet"
Return the names of all sheets (includes worksheets, chart sheets, macro sheets & dialog sheets) in ThisWorkbook:
Dim i As Integer
For i = 1 To ThisWorkbook.Sheets.count
MsgBox ThisWorkbook.Sheets(i).Name
Next i
Return the names of all worksheets (excludes chart sheets, macro sheets & dialog sheets) in the active workbook:
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
MsgBox ws.Name
Next
Example - Add a New Worksheet, and Refer to the new sheet.
Sub Worksheet_Add()
'add a new worksheet, and refer to the new sheet
'refer to the new sheet - use its object variable (Assign an Object to a Variable, using the Set Keyword):
'To use the object variable across procedures, declare it as a public variable (Public wsNew As Worksheet) in the first line at the top of the module.
Dim wsNew As Worksheet
Set wsNew = Worksheets.Add
wsNew.Name = "New WS 1"
'refer to the new sheet - the new sheet becomes the active sheet:
Worksheets.Add
ActiveSheet.Name = "New WS 2"
End Sub
Copy a Worksheet, Move or Change Worksheet Sequence, Hide or Display Worksheets, Remove or Delete a Worksheet
Copy a Worksheet
Use the Worksheet.Copy Method to copy a worksheet and place it, before or after another sheet in a workbook, or create a new workbook containing the copied sheet (use Sheets.Copy Method to copy a sheet). Syntax: WorksheetObject.Copy(Before, After). Both the arguments of Before & After are optional to specify, and you can specify only one of these at a time. These arguments refer to the sheet Before or After which the copied sheet will be placed, and omitting both arguments will create a new workbook containing the copied sheet. You can copy a worksheet to a location within the same workbook or to another workbook. See below illustrations on using this method.
In active workbook, copy worksheet named "Sheet1" and place it before "Sheet3":
Worksheets("Sheet1").Copy Before:=Sheets("Sheet3")
In active workbook, copy worksheet named "Sheet1" and place it after "Sheet3":
Worksheets("Sheet1").Copy After:=Sheets("Sheet3")
Copy "Sheet1" from active workbook and place it before "Objects" sheet in the "ExcelVBA.xlsm" workbook:
Worksheets("Sheet1").Copy Before:=Workbooks("ExcelVBA.xlsm").Sheets("Objects")
Copy "Sheet1" from active workbook, and a new workbook is created that contains the copied sheet:
Worksheets("Sheet1").Copy
Move or Change Worksheet Sequence
Use the Worksheet.Move Method to move a worksheet and place it, before or after another sheet in a workbook, or create a new workbook containing the moved sheet (use Sheets.Move Method to move a sheet). Syntax: WorksheetObject.Move(Before, After). Both the arguments of Before & After are optional to specify, and you can specify only one of these at a time. These arguments refer to the sheet Before or After which the moved sheet will be placed, and omitting both arguments will create a new workbook containing the moved sheet. You can move a worksheet to a location within the same workbook or to another workbook. Moving a worksheet changes the sequence of worksheets as the tabs appear or are visible within a workbook, but not in the Project Explorer in VBE. See below illustrations on using this method.
In active workbook, move worksheet named "Sheet1" and place it before "Sheet3":
Worksheets("Sheet1").Move Before:=Sheets("Sheet3")
In active workbook, move worksheet named "Sheet1" and place it after "Sheet3":
Worksheets("Sheet1").Move After:=Sheets("Sheet3")
Move "Sheet1" from active workbook and place it before "Objects" sheet in the "ExcelVBA.xlsm" workbook:
Worksheets("Sheet1").Move Before:=Workbooks("ExcelVBA.xlsm").Sheets("Objects")
Move "Sheet1" from active workbook, and a new workbook is created that contains the moved sheet:
Worksheets("Sheet1").Move
Hide or Display (make Visible) Worksheets
Use the Worksheet.Visible Property to hide a worksheet or to make it visible (use Sheets.Visible Property to hide or display sheets). You can simply use the code Worksheets("Sheet1").Visible = False to hide and Worksheets("Sheet1").Visible = True to make the worksheet visible. The XlSheetVisibility Enumeration is used to set or return a value indicating whether the worksheet is visible or hidden. The xlSheetHidden (value 0) setting hides a worksheet which can be made visible either by setting it to visible or by using the menu; the xlSheetVeryHidden (value 2) setting hides a worksheet which can be made visible only by setting it likewise and not with the menu through user-interface; the xlSheetVisible (value -1) setting will make the worksheet visible.
Example: Worksheet - Hide, make Visible, make Very Hidden.
Sub Worksheet_Visible1()
'hide, make visible, make very hidden & make visible - worksheet named "Sheet1"
'hides the worksheet named "Sheet1":
Worksheets("Sheet1").Visible = False
'returns the value 0, indicating "Sheet1" is hidden:
MsgBox Worksheets("Sheet1").Visible
'displays "Sheet1":
Worksheets("Sheet1").Visible = True
'returns the value -1, indicating "Sheet1" is visible:
MsgBox Worksheets("Sheet1").Visible
'makes "Sheet1" very hidden:
Worksheets("Sheet1").Visible = 2
'returns the value 2, "Sheet1" is very hidden:
MsgBox Worksheets("Sheet1").Visible
'displays "Sheet1":
Worksheets("Sheet1").Visible = xlSheetVisible
'returns the value -1, indicating "Sheet1" is visible:
MsgBox Worksheets("Sheet1").Visible
End Sub
Example: hide all but one sheet, make all sheets visible.
Sub Worksheet_Visible2()
'hide all but one sheet, make all sheets visible
Dim oSh As Object
Dim i As Long
'hide all sheets except the last sheet - note that all sheets in a workbook cannot be hidden at a time:
Sheets(Sheets.Count).Visible = True
For i = 1 To Sheets.Count - 1
Sheets(i).Visible = xlSheetHidden
Next i
'make all sheets in a workbook visible:
For Each oSh In Sheets
oSh.Visible = xlSheetVisible
Next
End Sub
Remove or Delete Worksheets
Use the Worksheet.Delete Method to delete or remove a worksheet in a workbook (use Sheets.Delete Method to delete sheets). Syntax: WorksheetObject.Delete. Using this method will display a dialog box which prompts the user to confirm or cancel the deletion. Setting the Application.DisplayAlerts Property to False will not display any prompts or alerts and in this case a default response will be chosen by Excel. This property is restored to its default value of True after the procedure ends. Use the code Application.DisplayAlerts = False to delete a worksheet without displaying the prompt.
Delete "Sheet1" in the active workbook, without displaying the prompt:
Application.DisplayAlerts = False
Sheets("Sheet1").Delete
We briefly discuss some options of Page Layout for a worksheet, & how to customize workbook Views, in this section.
Use the Worksheet.PageSetup Property for page setup settings of a worksheet. Page Setup attributes for the worksheet like orientation, margins, paper size, and so on can be set, and the worksheet printed accordingly. Using the PageSetup property returns a PageSetup object, and the attributes are set as properties of the PageSetup object viz. Orientation, PrintArea, LeftMargin, RightMargin, etc. are the properties of the PageSetup object. See example below on using this property to set Page Setup attributes.
Example - Set Page SetUp attributes and then print the worksheet.
Sub Worksheet_Page_Setup_Print()
'set Page SetUp attributes and then print the worksheet.
With Worksheets("Sheet3")
'set properties of the PageSetup object:
With .PageSetup
'set the row that contains the cells to be repeated at the top of each page:
.PrintTitleRows = Rows(1).Address
.Orientation = xlLandscape
.Zoom = 90
.PrintArea = "$D$1:$R$50"
'prints page number in header & center aligns the header:
.CenterHeader = "&P"
'sets the first page number to be used while printing the worksheet:
.FirstPageNumber = 2
.PaperSize = xlPaperA4
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.25)
'print cell gridlines when sheet is printed, using the PrintGridlines Property of the PageSetup Object:
.PrintGridlines = True
End With
'using the Worksheet.PrintOut Method to print the worksheet:
.PrintOut
End With
End Sub
Use the Worksheet.PrintPreview Method to view a preview of how the printed worksheet will look. Syntax: WorksheetObject.PrintPreview(EnableChanges). It is optional to specify the EnableChanges argument, which accepts a Boolean value (default value is True), to allow or disallow the user to change the page setup options (ex. page orientation, scaling, margins, etc) available in print preview. You can also apply the PrintPreview method to a Workbook object or a Range object, to view a preview of how the printed workbook or range will look respectively.
Example - PrintPreview
Sub PrintPreview()
'print preview of active sheet of workbook named "ExcelFile.xlsm, disallowing the user to change the page setup options available in print preview.
Workbooks("ExcelFile.xlsm").PrintPreview EnableChanges:=False
'print preview of "Sheet3" of workbook "ExcelFile.xlsm, allowing the user to change the page setup options available in print preview.
Workbooks("ExcelFile.xlsm").
Worksheets("Sheet3").PrintPreview EnableChanges:=True
End Sub
Use the Worksheet.DisplayPageBreaks Property to display both automatic and manual page breaks on a worksheet. This is a Boolean setting which can be set only if a printer is installed. The code ActiveSheet.DisplayPageBreaks = True will display page breaks on the active sheet.
Use the Window.View Property to set or return a workbook (active worksheet) view as it shows in the window. Syntax: WindowObject.View. You can have 3 settings for this property - xlNormalView (value 1), xlPageBreakPreview (value 2) & xlPageLayoutView (value 3) which respectively display workbook views of 'Normal', 'Page Layout' & 'Page Break'. See below example which illustrates these 3 views.
Example - Display worksheet views of 'Normal', 'Page Break' & 'Page Layout'.
Image 1a
Image 1b
Image 1c
Sub Worksheet_View()
'Illustrate worksheet window views of xlNormalView, xlPageBreakPreview & xlPageLayoutView
'refer Image 1a for original normal view, Image 1b for xlPageBreakPreview & Image 1c for xlPageLayoutView.
'activate worksheet:
Worksheets("Sheet11").Activate
With ActiveSheet
'using the Range.PageBreak Property, set the location of a manual break:
.Rows(9).PageBreak = xlPageBreakManual
.Columns("G").PageBreak = xlPageBreakManual
'set Page SetUp attributes - properties of the PageSetup object:
With .PageSetup
.Orientation = xlPortrait
.Zoom = 100
.PrintArea = "$A$1:$L$17"
'prints page number in header & center aligns the header:
.CenterHeader = "&P"
'sets the first page number to be used while printing the worksheet:
.FirstPageNumber = 1
End With
End With
'set Page Break view - refer Image 1b
ActiveWindow.View = xlPageBreakPreview
'reset to normal view:
ActiveSheet.Rows(9).PageBreak = xlPageBreakNone
ActiveSheet.Columns("G").PageBreak = xlPageBreakNone
ActiveSheet.DisplayPageBreaks = False
'entire sheet becomes the print area:
ActiveSheet.PageSetup.PrintArea = ""
'set Normal view - refer Image 1a:
ActiveWindow.View = xlNormalView
'set Page Layout view - refer Image 1c:
ActiveWindow.View = xlPageLayoutView
End Sub
Some worksheet display & view options like DisplayFormulaBar, DisplayFullScreen, DisplayHeadings, DisplayGridlines & FreezePanes are illustrated below.
Image 2a
Image 2b
Images 2a to 2c: refer Image 2a for a worksheet which displays both the Formula Bar & Headings; refer Image 2b for a worksheet which displays Headings but hides the Formula Bar; refer Image 2c for a worksheet which displays Formula Bar but hides the Headings.
Hide formula bar in Microsoft Excel (all worksheets, all workbooks), using the Application.DisplayFormulaBar Property:
Application.DisplayFormulaBar = False
Microsoft Excel (all worksheets, all workbooks) is in full-screen mode if set to True - use the Application.DisplayFullScreen Property:
Application.DisplayFullScreen = True
Hide the worksheet headings (only of the specific sheet), using the DisplayHeadings Property of the Window object:
ActiveWindow.DisplayHeadings = False
Hide display of gridlines in a worksheet (only of the specific sheet), using the DisplayGridlines Property (Note: the DisplayGridlines Property determines only the display of gridlines, and to determine printing or not of cell gridlines, use the PrintGridlines Property of the PageSetup Object):
ActiveWindow.DisplayGridlines = False
Freeze Panes (rows & columns) in a specified worksheet, using the Window.FreezePanes Property: below code keeps the first row visible & the first 2 columns (A & B) visible in the active worksheet, while the rest of the worksheet scrolls ie. a horizontal freeze line is effected between rows 1 and 2 and a vertical freeze line between columns B and C:
Range("C2").Select
ActiveWindow.FreezePanes = True
You might often use the following codes in your VBA Project - hide Formula Bar on all worksheets, on activating the workbook; display Formula Bar on all worksheets, on de-activating the workbook; hide display of headings on each worksheet, on opening the workbook. Enter the below codes in ThisWorkbook Module, in your VBE Code Window.
Private Sub Workbook_Activate()
Application.DisplayFormulaBar = False
End Sub
Private Sub Workbook_Deactivate()
Application.DisplayFormulaBar = True
End Sub
Private Sub Workbook_Open()
Dim ws As Worksheet
Dim wn As Window
For Each ws In ActiveWorkbook.Worksheets
ws.activate
For Each wn In ActiveWorkbook.Windows
wn.DisplayHeadings = False
Next
Next
'activate a specific worksheet:
Sheets("Sheet2").Activate
End Sub
Application.Calculation Property returns or sets the calculation mode in Microsoft Excel (all open workbooks). There are 3 settings: xlCalculationAutomatic - (Default) Automatic recalculation by Excel as data is entered in cells; xlCalculationSemiautomatic - Automatic recalculation by Excel except for Data Tables; xlCalculationManual - Calculation is done only when requested by user on clicking "Calculate Now" or "Calculate Sheet" or pressing F9, or with vba code.
Set Manual Calculation mode:
Application.Calculation = xlCalculationManual
Calculate Method. Apply the Calculate Method to a Worksheet object to calculate a specific worksheet in a workbook, or apply this method to an Application object to calculate all open workbooks, or you can apply this method to a Range Collection by specifying a cell or a range of cells in a worksheet.
Calculate Method as applicable to the Application Object
Calculate all workbooks that are open:
Application.Calculate
Calculate
Calculate Method as applicable to the Worksheet Object
Calculate a specified worksheet (named "Sheet1"):
Application.Worksheets("Sheet1").Calculate
Worksheets("Sheet1").Calculate
Calculate Method as applicable to the Range of Cells
Calculate the specified range (A5:B6 - cells A5, A6, B5 & B6) in a worksheet:
Worksheets("Sheet1").Range("A5:B6").Calculate
Calculate the entire column (column A) in a worksheet:
Worksheets("Sheet1").Columns(1).Calculate
Calculate non-contiguous cells A5, A6, B7 & B20 in the active worksheet:
Range("A5,A6,B7,B20").Calculate
Speed up your VBA Code by Turning off Screen Updates and Automatic Calculations
In Excel, the default calculation mode is Automatic Mode (Application.Calculation = xlCalculationAutomatic), wherein Excel automatically calculates each cell as you enter it. When Excel is in Manual Mode (Application.Calculation = xlCalculationManual), calculation is done only when requested by the user viz. on clicking "Calculate Now" or pressing F9 or changing the calculation mode to Automatic. While in Automatic Mode, for each new value entered by a macro, Excel will recalculate all cells referred to or impacted by the new value thereby slowing the execution of the vba code. This can slow down the working of the vba code considerably, especially in the case of large macros wherein calculations are significant. To speed up a macro and make the execution faster & efficient, it is typical of developers to turn off automatic calculation right at the start of the macro, recalculate the specific worksheet or range using the Calculate method within the macro, and then turn on the automatic calculation back at the end of the code.
The following Example turns off Screen Updates and Automatic Calculations & use the Calculate Method, while running their vba codes (macros):
Sub Calculate()
'turn off Screen Updates and Automatic Calculations & use the Calculate Method, while running code.
'turn off Screen Updates and Automatic Calculations:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Enter Your Code Here
'use the Calculate method to calculate all open workbooks:
Application.Calculate
'turn on Screen Updates and Automatic Calculations:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Set the Application.CalculateBeforeSave Property to True, to calculate workbooks before saving them to disk if the Calculation property has been set to manual by specifying xlCalculationManual. The CalculateBeforeSave Property accepts Boolean values (True/False) and is not affected by changing the Calculation property. In this case, you will enter the following code in your macro:
Application.Calculation = xlCalculationManual
Application.CalculateBeforeSave = True
Set the Worksheet.EnableCalculation Property to True, to automatically recalculate the worksheet. When this property is set to False, Excel does not recalculate the sheet, and when the setting is changed from False to True, Excel recalculates the worksheet. Note that this property is not preserved when the workbook is closed, and will revert to its default True value on opening the workbook & hence you will need to reset with vba code every time you open the workbook. To control calculations for each worksheet, you can set the EnableCalculation Property to False for the particular worksheets which you do not want to calculate, and then set the Application.Calculation property to xlCalculationAutomatic. Note that running the Calculate method on a worksheet for which the EnableCalculation Property is set to False, will not calculate that worksheet. The following code will automatically calculate all worksheets except "Sheet1":
Worksheets("Sheet1").EnableCalculation = False
Application.Calculation = xlCalculationAutomatic