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
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
Here is an example of processing the Worksheet items in a collection.
Dim ws As Worksheet For Each ws In Worksheets Next ws
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
These are the main methods of the Worksheet class
Sheets.Delete - Deletes the object.
Activate - Makes the current sheet the active sheet.
Delete - Deletes the object.
Worksheets.Delete - Deletes the object.
Dim wrks As Worksheets: Set wrks = wrks.Delete
Sheets.FillAcrossSheets - Copies a range to the same area on all other worksheets in a collection.
Sheets.Move - Moves the sheet to another location in the workbook.
Calculate - Calculates all open workbooks, a specific worksheet in a workbook, or a specified range of cells on a worksheet, as shown in the following table.
ChartObjects - Returns an chartobjects that represents either a single embedded chart (a Chartchartobjects chartobjects) or a collection of all the embedded charts (a Chartchartobjectss chartobjects) on the sheet.
CheckSpelling - Checks the spelling of an object.
CircleInvalid - Circles invalid entries on the worksheet.
ClearArrows - Clears the tracer arrows from the worksheet. Tracer arrows are added by using the auditing feature.
ClearCircles - Clears circles from invalid entries on the worksheet.
Evaluate - Converts a Microsoft Excel name to an object or a value.
ExportAsFixedFormat - Exports to a file of the specified format.
Move - Moves the sheet to another location in the workbook.
OLEObjects - Returns an oleobjects that represents either a single OLE oleobjects (an OLEoleobjects) or a collection of all OLE oleobjectss (an OLEoleobjectss collection) on the chart or sheet. Read-only.
Paste - Pastes the contents of the Clipboard onto the sheet.
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.
PivotTables - Returns an pivottables that represents either a single PivotTable report (a PivotTable pivottables) or a collection of all the PivotTable reports (a PivotTables pivottables) on a worksheet. Read-only.
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.
SaveAs - Saves changes to the chart or worksheet in a different file.
Scenarios - Returns an scenarios that represents either a single scenario (a Scenario scenarios) or a collection of scenarios (a Scenarios scenarios) on the worksheet.
SetBackgroundPicture - Sets the background graphic for a worksheet.
XmlDataQuery - Returns a Range object that represents the cells mapped to a particular XPath. Returns Nothing if the specified XPath has not been mapped to the worksheet, or if the mapped range is empty.
XmlMapQuery - Returns a Range object that represents the cells mapped to a particular XPath. Returns Nothing if the specified XPath has not been mapped to the worksheet.
Worksheets.FillAcrossSheets - Copies a range to the same area on all other worksheets in a collection.
Worksheets.Move - Moves the sheet to another location in the workbook.
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.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.
AutoFilterMode true if the AutoFilter drop-down arrows are currently displayed on the sheet. This property is independent of the FilterMode property.
Cells returns a Range object that represents all the cells on the worksheet (not just the cells that are currently in use).
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.
CodeName returns the code name for the object.
Columns returns a Range object that represents all the columns on the specified worksheet.
Comments returns a Comments collection that represents all the comments for the specified worksheet.
CommentsThreaded returns a CommentsThreaded collection that represents all the top-level/root comments (no replies) for the specified worksheet. Includes legacy and modern comments.
ConsolidationFunction returns the function code used for the current consolidation. Can be one of the constants of XlConsolidationFunction.
ConsolidationOptions returns a three-element array of consolidation options, as shown in the following table. If the element is True, that option is set.
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.
CustomProperties returns a CustomProperties object representing the identifier information associated with a worksheet.
EnableAutoFilter true if AutoFilter arrows are enabled when user-interface-only protection is turned on.
FilterMode true if the worksheet is in the filter mode.
Hyperlinks returns a Hyperlinks collection that represents the hyperlinks for the worksheet.
Index returns a Long value that represents the index number of the object within the collection of similar objects.
ListObjects returns a collection of ListObject objects on the worksheet.
MailEnvelope represents an email header for a document.
Name returns or sets a String value that represents the object name.
Names returns a Names collection that represents all the worksheet-specific names (names defined with the "WorksheetName!" prefix).
Next returns a Worksheet object that represents the next sheet.
Outline returns an Outline object that represents the outline for the specified worksheet.
Parent returns the parent workbook for the specified workbook. Read-only.
Previous returns a Worksheet object that represents the previous sheet.
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.
ProtectDrawingObjects true if shapes are protected. To turn on shape protection, use the Protect method with the DrawingObjects argument set to True.
Protection returns a Protection object that represents the protection options of the worksheet.
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.
ProtectScenarios true if the worksheet scenarios are protected.
QueryTables returns the QueryTables collection that represents all the query tables on the specified worksheet.
Range returns a Range object that represents a cell or a range of cells.
Rows returns a Range object that represents all the rows on the specified worksheet.
ScrollArea returns or sets the range where scrolling is allowed, as an A1-style range reference. Cells outside the scroll area cannot be selected.
Shapes returns a Shapes collection that represents all the shapes on the worksheet.
Sort returns a Sort object.
StandardHeight returns the standard (default) height of all the rows on the worksheet, in points.
StandardWidth returns or sets the standard (default) width of all the columns on the worksheet.
Tab returns a Tab object for a worksheet.
TransitionExpEval true if Microsoft Excel uses Lotus 1-2-3 expression evaluation rules for the worksheet.
TransitionFormEntry true if Microsoft Excel uses Lotus 1-2-3 formula entry rules for the worksheet.
Type returns an XlSheetType value that represents the worksheet type.
UsedRange returns a Range object that represents the used range on the specified worksheet.
Visible returns or sets an XlSheetVisibility value that determines whether the object is visible.
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.
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.