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 = ActiveSheet

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 wsChart As Worksheet
For Each wsChart In Charts
	
Next wsChart

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
Select with its procedures Sheets.Select, Select and Worksheets.Select
Display/Show with its procedures DisplayRightToLeft, ShowAllData and ShowDataForm
Enable with its procedures EnableCalculation, EnableFormatConditionsCalculation, EnableOutlining, EnablePivotTable and EnableSelection
Protect with its procedures Protect and Unprotect

Methods

These are the main methods of the Worksheet class

Sheets.Delete - Deletes the object.

Charts.Delete

Activate - Makes the current sheet the active sheet.

ActiveSheet.Activate

Delete - Deletes the object.

ActiveSheet.Delete

Worksheets.Delete - Deletes the object.

Dim wrks As Worksheets: Set wrks = 
wrks.Delete

Other Methods

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

Charts.FillAcrossSheets Range:=

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

Charts.Move

CheckSpelling - Checks the spelling of an object.

ActiveSheet.CheckSpelling

ExportAsFixedFormat - Exports to a file of the specified format.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF

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

ActiveSheet.Move

Paste - Pastes the contents of the Clipboard onto the sheet.

ActiveSheet.Paste

PasteSpecial - Pastes the contents of the Clipboard onto the sheet, using a specified format. Use this method to paste data from other applications or to paste data in a specific format.

ActiveSheet.PasteSpecial

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.

Dim pvtPivotTableWizard As PivotTable
Set pvtPivotTableWizard = ActiveSheet.PivotTableWizard(xlDatabase, Range("A1:C100"))

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

Dim strFilename As String: strFilename = 
ActiveSheet.SaveAs Filename:=strFilename

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

Dim wrks As Worksheets: Set wrks = 
wrks.FillAcrossSheets Range:=

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

Dim wrks As Worksheets: Set wrks = 
wrks.Move

Properties

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

Dim lngCount As Long
lngCount = Charts.Count

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

Dim chrItem As Chart
Set chrItem = Charts(Index:=1)

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

Dim wbParent As Workbook
Set wbParent = Charts.Parent

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

Charts.Visible = True

AutoFilter returns an AutoFilter object if filtering is on.

Dim afrAutoFilter As AutoFilter
Set afrAutoFilter = ActiveSheet.AutoFilter

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

ActiveSheet.AutoFilterMode = True

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

Dim rngCells As Range
Set rngCells = ActiveSheet.Cells

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.

Dim rngCircularReference As Range
Set rngCircularReference = ActiveSheet.CircularReference

CodeName returns the code name for the object.

Dim strCodeName As String
strCodeName = ActiveSheet.CodeName

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

Dim rngColumns As Range
Set rngColumns = ActiveSheet.Columns

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

Dim cmmsComments As Comments
Set cmmsComments = ActiveSheet.Comments

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

Dim ctdCommentsThreaded As CommentsThreaded
Set ctdCommentsThreaded = ActiveSheet.CommentsThreaded

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

Dim xcfConsolidationFunction As XlConsolidationFunction
xcfConsolidationFunction = ActiveSheet.ConsolidationFunction

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

Dim varConsolidationOptions As Variant
varConsolidationOptions = ActiveSheet.ConsolidationOptions

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.

Dim varConsolidationSources As Variant
varConsolidationSources = ActiveSheet.ConsolidationSources

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

Dim cpsCustomProperties As CustomProperties
Set cpsCustomProperties = ActiveSheet.CustomProperties

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

ActiveSheet.EnableAutoFilter = True

FilterMode true if the worksheet is in the filter mode.

Dim booFilterMode As Boolean
booFilterMode = ActiveSheet.FilterMode

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

Dim hypsHyperlinks As Hyperlinks
Set hypsHyperlinks = ActiveSheet.Hyperlinks

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

Dim lngIndex As Long
lngIndex = ActiveSheet.Index

ListObjects returns a collection of ListObject objects on the worksheet.

Dim losListObjects As ListObjects
Set losListObjects = ActiveSheet.ListObjects

MailEnvelope represents an email header for a document.

Dim meeMailEnvelope As Office.MsoEnvelope
Set meeMailEnvelope = ActiveSheet.MailEnvelope

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

ActiveSheet.Name =

NamedSheetViews

Dim As Variant
ActiveSheet.NamedSheetViews

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

Dim nmsNames As Names
Set nmsNames = ActiveSheet.Names

Next returns a Worksheet object that represents the next sheet.

Dim wsNext As Worksheet
Set wsNext = ActiveSheet.Next

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

Dim otlOutline As Outline
Set otlOutline = ActiveSheet.Outline

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

Dim wbParent As Workbook
Set wbParent = ActiveSheet.Parent

Previous returns a Worksheet object that represents the previous sheet.

Dim wsPrevious As Worksheet
Set wsPrevious = ActiveSheet.Previous

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.

Dim booProtectContents As Boolean
booProtectContents = ActiveSheet.ProtectContents

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

Dim booProtectDrawingObjects As Boolean
booProtectDrawingObjects = ActiveSheet.ProtectDrawingObjects

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

Dim prtProtection As Protection
Set prtProtection = ActiveSheet.Protection

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.

Dim booProtectionMode As Boolean
booProtectionMode = ActiveSheet.ProtectionMode

ProtectScenarios true if the worksheet scenarios are protected.

Dim booProtectScenarios As Boolean
booProtectScenarios = ActiveSheet.ProtectScenarios

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

Dim qtsQueryTables As QueryTables
Set qtsQueryTables = ActiveSheet.QueryTables

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

Dim strCell1 As String: strCell1 = 
Dim rngRange As Range
Set rngRange = ActiveSheet.Range(Cell1:=strCell1)

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

Dim rngRows As Range
Set rngRows = ActiveSheet.Rows

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

ActiveSheet.ScrollArea =

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

Dim shpsShapes As Shapes
Set shpsShapes = ActiveSheet.Shapes

Sort returns a Sort object.

Dim srtSort As Sort
Set srtSort = ActiveSheet.Sort

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

Dim dblStandardHeight As Double
dblStandardHeight = ActiveSheet.StandardHeight

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

ActiveSheet.StandardWidth =

Tab returns a Tab object for a worksheet.

Dim tabTab As Tab
Set tabTab = ActiveSheet.Tab

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

ActiveSheet.TransitionExpEval = True

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

ActiveSheet.TransitionFormEntry = True

Type returns an XlSheetType value that represents the worksheet type.

Dim xstType As XlSheetType
xstType = ActiveSheet.Type

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

Dim rngUsedRange As Range
Set rngUsedRange = ActiveSheet.UsedRange

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

ActiveSheet.Visible = xlSheetHidden

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

Dim wrks As Worksheets: Set wrks = 
Dim lngCount As Long
lngCount = wrks.Count

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

Dim wrks As Worksheets: Set wrks = 
Dim wsItem As Worksheet
Set wsItem = wrks(Index:=1)

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

Dim wrks As Worksheets: Set wrks = 
Dim objParent As Object
Set objParent = wrks.Parent

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

Dim wrks As Worksheets: Set wrks = 
wrks.Visible = True