Class Worksheet (Excel VBA)
The class Worksheet represents a worksheet.
The classes Application, Chart, Range, Window and Workbook. give access to class Worksheet
To use a Worksheet class variable it first needs to be instantiated, example below, for alternative ways see: Set a Worksheet using VBA
Dim ws as Worksheet
Set ws = ActiveSheet
The following procedures can be used to set variables of type Worksheet: Application.ActiveSheet, Chart.Next, Chart.Previous, HPageBreak.Parent, Range.Parent, Range.Worksheet, Sheets.Add, Sheets.Item, Slicer.Parent, VPageBreak.Parent, Window.ActiveSheet, Workbook.ActiveSheet, Next and Previous ...
.For Each
Here is an example of processing the Worksheet items in a collection.
Dim ws As Worksheet
For Each ws In Worksheets
Next ws
Methods
Delete - Deletes the object.
ActiveSheet.Delete
ExportAsFixedFormat - Exports to a file of the specified format.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF
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.
Worksheets("Sheet1").Range("D1").Select
ActiveSheet.PasteSpecial format:= _
"Microsoft Word 8.0 Document Object"
PrintOut - Prints the object.
ActiveSheet.PrintOut
Protect - Protects a worksheet so that it cannot be modified.
ActiveSheet.Protect
SaveAs - Saves changes to the chart or worksheet in a different file.
Dim strFilename As String: strFilename =
ActiveSheet.SaveAs Filename:=strFilename
Select - Selects the object.
ActiveSheet.Select
Unprotect - Removes protection from a sheet or workbook. This method has no effect if the sheet or workbook isn't protected.
ActiveSheet.Unprotect
/code>Activate - Makes this sheet the active sheet.
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 ChartObject object) or a collection of all the embedded charts (a ChartObjects object) 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.
Copy - Copies the sheet to another location in the current workbook or a new workbook.
Evaluate - Converts a Microsoft Excel name to an object or a value.
Move - Moves the sheet to another location in the workbook.
OLEObjects - Returns an oleobjects that represents either a single OLE object (an OLEObject) or a collection of all OLE objects (an OLEObjects collection) on the chart or sheet. Read-only.
Paste - Pastes the contents of the Clipboard onto the sheet.
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.
PrintPreview - Shows a preview of the object as it would look when printed.
ResetAllPageBreaks - Resets all page breaks on the specified worksheet.
Scenarios - Returns an scenarios that represents either a single scenario (a Scenario) or a collection of scenarios (a Scenarios collection) on the worksheet.
SetBackgroundPicture - Sets the background graphic for a worksheet.
ShowAllData - Makes all rows of the currently filtered list visible. If AutoFilter is in use, this method changes the arrows to "All.
ShowDataForm - Displays the data form associated with the 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.
Properties
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.
DisplayPageBreaks true if page breaks (both automatic and manual) on the specified worksheet are displayed.
DisplayRightToLeft true if the specified worksheet is displayed from right to left instead of from left to right. False if the object is displayed from left to right.
EnableAutoFilter true if AutoFilter arrows are enabled when user-interface-only protection is turned on.
EnableCalculation true if Microsoft Excel automatically recalculates the worksheet when necessary. False if Excel doesn't recalculate the sheet.
EnableFormatConditionsCalculation returns or sets if conditional formats occur automatically as needed.
EnableOutlining true if outlining symbols are enabled when user-interface-only protection is turned on.
EnablePivotTable true if PivotTable controls and actions are enabled when user-interface-only protection is turned on.
EnableSelection returns or sets what can be selected on the sheet.
FilterMode true if the worksheet is in the filter mode.
HPageBreaks returns an HPageBreaks collection that represents the horizontal page breaks on the sheet.
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.
PageSetup returns a PageSetup object that contains all the page setup settings for the specified object.
Parent returns the parent workbook for the specified workbook. Read-only.
Previous returns a Worksheet object that represents the previous sheet.
PrintedCommentPages returns the number of comment pages that will be printed for the current worksheet.
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.
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.
VPageBreaks returns a VPageBreaks collection that represents the vertical page breaks on the sheet.
Related Classes
AutoFilter - Represents autofiltering for the specified worksheet.
ChartObjects - A collection of all the ChartObject objects on the specified chart sheet, dialog sheet, or worksheet.
Comments - A collection of cell comments.
CommentsThreaded - A collection of top-level CommentThreaded objects in a Worksheet, or a collection of replies in a single threaded comment.
CustomProperties - A collection of CustomProperty objects that represents additional information. The information can be used as metadata for XML.
HPageBreaks - The collection of horizontal page breaks within the print area.
Hyperlinks - Represents the collection of hyperlinks for a worksheet or range.
ListObjects - A collection of all the ListObject objects on a worksheet. Each ListObject object represents a table on the worksheet.
Names - A collection of all the Name objects in the application or workbook.
OLEObjects - A collection of all the OLEObject objects on the specified worksheet.
Outline - Represents an outline on a worksheet.
PageSetup - Represents the page setup description.
PivotTable - Represents a PivotTable report on a worksheet.
PivotTables - A collection of all the PivotTable objects in the specified workbook.
Protection - Represents the various types of protection options available for a worksheet.
QueryTables - A collection of QueryTable objects.
Range - Represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3D range.
Scenarios - A collection of all the Scenario objects on the specified worksheet.
Shapes - A collection of all the Shape objects on the specified sheet.
Sort - Represents a sort of a range of data.
Tab - Represents the tab of a chart or a worksheet.
VPageBreaks - A collection of vertical page breaks within the print area.