Class Worksheet (Excel VBA)

The class Worksheet represents a worksheet.

The main procedures of class Worksheet are Sheets.Add, Sheets.Copy, Sheets.Delete, Sheets.Select, Activate, Copy, Delete, Select, Worksheets.Add, Worksheets.Copy, Worksheets.Delete and Worksheets.Select

Set

To use a Worksheet class variable it first needs to be instantiated, for example

Dim ws As Worksheet
Set ws = Application.Sheets.Add()

The following procedures can be used to set variables of type Worksheet: Application.Charts, Application.Sheets, Application.Worksheets, Window.SelectedSheets, Workbook.Charts, Workbook.Sheets, Workbook.Worksheets, Application.ActiveSheet, Chart.Next, Chart.Previous, HPageBreak.Parent, Range.Parent, Range.Worksheet, Sheets.Item, Sheets.Item, Sheets.Add, Sheets.Add, Slicer.Parent, VPageBreak.Parent, Window.ActiveSheet, Workbook.ActiveSheet, Next, Previous, Worksheets.Item and Worksheets.Add

For Each

Here is an example of processing the Worksheet items in a collection.

Dim wsSheet As Worksheet
For Each wsSheet In Application.Sheets
	
Next wsSheet

Themes

Some procedures in this class have been grouped together in themes and are described on separate theme pages

Add with its procedures Sheets.Add and Worksheets.Add
Copy with its procedures Sheets.Copy, Copy and Worksheets.Copy
Print with its procedures Sheets.HPageBreaks, Sheets.PrintOut, Sheets.PrintPreview, Sheets.VPageBreaks, DisplayPageBreaks, HPageBreaks, PageSetup, PrintedCommentPages, PrintOut, PrintPreview, ResetAllPageBreaks, VPageBreaks, Worksheets.HPageBreaks, Worksheets.PrintOut, Worksheets.PrintPreview and Worksheets.VPageBreaks
Display/Show with its procedures DisplayRightToLeft, ShowAllData and ShowDataForm
Enable with its procedures EnableCalculation, EnableFormatConditionsCalculation, EnableOutlining, EnablePivotTable and EnableSelection
Paste with its procedures Paste and PasteSpecial
Protect with its procedures Protect and Unprotect
XML with its procedures XmlDataQuery and XmlMapQuery

Methods

These are the main methods of the Worksheet class

Sheets.Delete - Deletes the object.

Sheets.Select - Selects the object.

Activate - Makes the current sheet the active sheet.

Worksheets("Sheet1").Activate

Delete - Deletes the object.

Select - Selects the object.

Worksheets.Delete - Deletes the object.

Worksheets.Select - Selects the object.

Other Methods

Sheets.FillAcrossSheets - Copies a range to the same area on all other worksheets in a collection.

x = Array("Sheet1", "Sheet5", "Sheet7") 
Sheets(x).FillAcrossSheets _ 
 Worksheets("Sheet1").Range("A1:C5")

Sheets.Move - Moves the sheet to another location in the workbook.

Worksheets("Sheet1").Move _ 
 after:=Worksheets("Sheet3")

CheckSpelling - Checks the spelling of an object.

ExportAsFixedFormat - Exports to a file of the specified format.

Move - Moves the sheet to another location in the workbook.

Worksheets("Sheet1").Move _ 
 after:=Worksheets("Sheet3")

PivotTableWizard - Creates a new PivotTable report. This method doesn't display the PivotTable Wizard. This method isn't available for OLE DB data sources. Use the Add method to add a PivotTable cache, and then create a PivotTable report based on the cache.

ActiveSheet.PivotTableWizard xlDatabase, Range("A1:C100")

SaveAs - Saves changes to the chart or worksheet in a different file.

Worksheets.FillAcrossSheets - Copies a range to the same area on all other worksheets in a collection.

x = Array("Sheet1", "Sheet5", "Sheet7") 
Sheets(x).FillAcrossSheets _ 
 Worksheets("Sheet1").Range("A1:C5")

Worksheets.Move - Moves the sheet to another location in the workbook.

Worksheets("Sheet1").Move _ 
 after:=Worksheets("Sheet3")

Properties

Sheets.Count returns a Long value that represents the number of objects in the collection.

Sheets.Item returns a single Object object from the collection.

Sheets.Item("sheet1").Activate

Sheets.Parent returns the parent object for the specified object. Read-only.

Sheets.Visible returns or sets a Variant value that determines whether the boolean is visible.

AutoFilter returns an AutoFilter object if filtering is on.

Dim Worksheet1 As Worksheet 
 
Dim returnValue As AutoFilter 
Set returnValue = Worksheet1.AutoFilter

AutoFilterMode true if the AutoFilter drop-down arrows are currently displayed on the sheet. This property is independent of the FilterMode property.

If Worksheets("Sheet1").AutoFilterMode Then 
 isOn = "On" 
Else 
 isOn = "Off" 
End If 
MsgBox "AutoFilterMode is " & isOn

Cells returns a Range object that represents all the cells on the worksheet (not just the cells that are currently in use).

Worksheets("Sheet1").Cells(5, 3).Font.Size = 14

CircularReference returns a Range object that represents the range containing the first circular reference on the sheet, or returns Nothing if there's no circular reference on the sheet. The circular reference must be removed before calculation can proceed.

Worksheets("Sheet1").CircularReference.Select

CodeName returns the code name for the object.

Worksheets(1).Range("a1") 
Sheet1.Range("a1")

Columns returns a Range object that represents all the columns on the specified worksheet.

Worksheets("Sheet1").Columns(1).Font.Bold = True

Comments returns a Comments collection that represents all the comments for the specified worksheet.

For Each c in ActiveSheet.Comments 
 If c.Author = "Jean Selva" Then c.Delete 
Next

CommentsThreaded returns a CommentsThreaded collection that represents all the top-level/root comments (no replies) for the specified worksheet. Includes legacy and modern comments.

For Each c in ActiveSheet.CommentsThreaded
 If c.Author.Name = "Jean Selva" Then c.Delete 
Next

ConsolidationFunction returns the function code used for the current consolidation. Can be one of the constants of XlConsolidationFunction.

If Worksheets("Sheet1").ConsolidationFunction = xlSum Then 
 MsgBox "Sheet1 uses the SUM function for consolidation." 
End If

ConsolidationOptions returns a three-element array of consolidation options, as shown in the following table. If the element is True, that option is set.

Set newSheet = Worksheets.Add 
aOptions = Worksheets("Sheet1").ConsolidationOptions 
newSheet.Range("A1").Value = "Use labels in top row" 
newSheet.Range("A2").Value = "Use labels in left column" 
newSheet.Range("A3").Value = "Create links to source data" 
For i = 1 To 3 
 If aOptions(i) = True Then 
 newSheet.Cells(i, 2).Value = "True" 
 Else 
 newSheet.Cells(i, 2).Value = "False" 
 End If 
Next i 
newSheet.Columns("A:B").AutoFit

ConsolidationSources returns an array of string values that name the source sheets for the worksheet's current consolidation. Returns Empty if there's no consolidation on the sheet.

Set newSheet = Worksheets.Add 
newSheet.Range("A1").Value = "Consolidation Sources" 
aSources = Worksheets("Sheet1").ConsolidationSources 
If IsEmpty(aSources) Then 
 newSheet.Range("A2").Value = "none" 
Else 
 For i = 1 To UBound(aSources) 
 newSheet.Cells(i + 1, 1).Value = aSources(i) 
 Next i 
End If 
newSheet.Columns("A:B").AutoFit

CustomProperties returns a CustomProperties object representing the identifier information associated with a worksheet.

Sub CheckCustomProperties() 
 
 Dim wksSheet1 As Worksheet 
 
 Set wksSheet1 = Application.ActiveSheet 
 
 ' Add metadata to worksheet. 
 wksSheet1.CustomProperties.Add _ 
 Name:="Market", Value:="Nasdaq" 
 
 ' Display metadata. 
 With wksSheet1.CustomProperties.Item(1) 
 MsgBox .Name & vbTab & .Value 
 End With 
 
End Sub

EnableAutoFilter true if AutoFilter arrows are enabled when user-interface-only protection is turned on.

ActiveSheet.EnableAutoFilter = True 
ActiveSheet.Protect contents:=True, userInterfaceOnly:=True

FilterMode true if the worksheet is in the filter mode.

Dim Worksheet1 As Worksheet 
 
Dim returnValue As Boolean 
returnValue = Worksheet1.FilterMode

Hyperlinks returns a Hyperlinks collection that represents the hyperlinks for the worksheet.

For Each h in Worksheets(1).Hyperlinks 
 If Instr(h.Name, "Microsoft") <> 0 Then h.Follow 
Next

Index returns a Long value that represents the index number of the object within the collection of similar objects.

Sub DisplayTabNumber() 
 Dim strSheetName as String 
 
 strSheetName = InputBox("Type a sheet name, such as Sheet4.") 
 
 MsgBox "This sheet is tab number " & Sheets(strSheetName).Index 
End Sub

ListObjects returns a collection of ListObject objects on the worksheet.

MailEnvelope represents an email header for a document.

Sub HeaderComments() 
 
 ActiveSheet.MailEnvelope.Introduction = "To Whom It May Concern: " 
 
End Sub

Name returns or sets a String value that represents the object name.

' This macro sets today's date as the name for the current sheet 
Sub NameWorksheetByDate() 
    'Changing the sheet name to today's date
    ActiveSheet.Name = Format(Now(), "dd-mm-yyyy")

    'Changing the sheet name to a value from a cell
    ActiveSheet.Name = ActiveSheet.Range("A1").value
End Sub

NamedSheetViews

Names returns a Names collection that represents all the worksheet-specific names (names defined with the "WorksheetName!" prefix).

ActiveWorkbook.Names.Add Name:="myName", RefersToR1C1:= _ 
 "=Sheet1!R1C1"

Next returns a Worksheet object that represents the next sheet.

Worksheets("Sheet1").Activate 
ActiveCell.Next.Select 

Outline returns an Outline object that represents the outline for the specified worksheet.

Worksheets("Sheet1").Outline.AutomaticStyles = True

Parent returns the parent workbook for the specified workbook. Read-only.

Previous returns a Worksheet object that represents the previous sheet.

Worksheets("Sheet1").Activate 
ActiveCell.Previous.Select

ProtectContents true if the contents of the sheet are protected. This protects the individual cells. To turn on content protection, use the Protect method with the Contents argument set to True.

If Worksheets("Sheet1").ProtectContents = True Then 
 MsgBox "The contents of Sheet1 are protected." 
End If

ProtectDrawingObjects true if shapes are protected. To turn on shape protection, use the Protect method with the DrawingObjects argument set to True.

If Worksheets("Sheet1").ProtectDrawingObjects = True Then 
 MsgBox "The shapes on Sheet1 are protected." 
End If

Protection returns a Protection object that represents the protection options of the worksheet.

Sub CheckProtection() 
 
 ActiveSheet.Protect 
 
 ' Check the ability to insert columns on a protected sheet. 
 ' Notify the user of this status. 
 If ActiveSheet.Protection.AllowInsertingColumns = True Then 
 MsgBox "The insertion of columns is allowed on this protected worksheet." 
 Else 
 MsgBox "The insertion of columns is not allowed on this protected worksheet." 
 End If 
 
End Sub

ProtectionMode true if user-interface-only protection is turned on. To turn on user interface protection, use the Protect method with the UserInterfaceOnly argument set to True.

MsgBox ActiveSheet.ProtectionMode

ProtectScenarios true if the worksheet scenarios are protected.

If Worksheets("Sheet1").ProtectScenarios Then _ 
 MsgBox "Scenarios are protected on this worksheet."

QueryTables returns the QueryTables collection that represents all the query tables on the specified worksheet.

For Each qt in Worksheets(1).QueryTables 
 qt.Refresh 
Next

Range returns a Range object that represents a cell or a range of cells.

Worksheets("Sheet1").Range("A1").Value = 3.14159

Rows returns a Range object that represents all the rows on the specified worksheet.

Worksheets("Sheet1").Rows(3).Delete

ScrollArea returns or sets the range where scrolling is allowed, as an A1-style range reference. Cells outside the scroll area cannot be selected.

Worksheets(1).ScrollArea = "a1:f10"

Shapes returns a Shapes collection that represents all the shapes on the worksheet.

With Worksheets(1).Shapes.AddLine(10, 10, 250, 250).Line 
 .DashStyle = msoLineDashDotDot 
 .ForeColor.RGB = RGB(50, 0, 128) 
End With

Sort returns a Sort object.

StandardHeight returns the standard (default) height of all the rows on the worksheet, in points.

Worksheets("Sheet1").Rows(1).RowHeight = _ 
 Worksheets("Sheet1").StandardHeight

StandardWidth returns or sets the standard (default) width of all the columns on the worksheet.

Worksheets("Sheet1").Columns(1).ColumnWidth = _ 
 Worksheets("Sheet1").StandardWidth

Tab returns a Tab object for a worksheet.

Sub CheckTab() 
 
 ' Determine if color index of 1st tab is set to none. 
 If Worksheets(1).Tab.ColorIndex = xlColorIndexNone Then 
 MsgBox "The color index is set to none for the 1st " & _ 
 "worksheet tab." 
 Else 
 MsgBox "The color index for the tab of the 1st worksheet " & _ 
 "is not set none." 
 End If 
 
End Sub

TransitionExpEval true if Microsoft Excel uses Lotus 1-2-3 expression evaluation rules for the worksheet.

Worksheets("Sheet1").TransitionExpEval = True

TransitionFormEntry true if Microsoft Excel uses Lotus 1-2-3 formula entry rules for the worksheet.

Worksheets("Sheet1").TransitionFormEntry = True

Type returns an XlSheetType value that represents the worksheet type.

UsedRange returns a Range object that represents the used range on the specified worksheet.

Worksheets("Sheet1").Activate 
ActiveSheet.UsedRange.Select

Visible returns or sets an XlSheetVisibility value that determines whether the object is visible.

Worksheets("Sheet1").Visible = False

Worksheets.Count returns a Long value that represents the number of objects in the collection.

Worksheets.Item returns a single Worksheet object from the collection.

ActiveWorkbook.Worksheets.Item(1) 
ActiveWorkbook.Worksheets(1)

Worksheets.Parent returns the parent object for the specified object. Read-only.

Worksheets.Visible returns or sets a Variant value that determines whether the boolean is visible.