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.