How to use Excel class Worksheet

Set Worksheet

You can Set a Worksheet variable by calling a method that returns a variable of that type, for example:

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets("Locations")
If ws Is Nothing Then
    Exit Sub 'possible way of handing no worksheet was set
End If

Set worksheet discusses the methods can be used to set a Worksheet variable - read more set worksheet

Add Worksheet

The code below shows how you can create a new Worksheet and give it a name.

Dim wb As Workbook: Set wb =
Dim strName As String: strName =
Dim ws As Worksheet
Set ws = wb.Worksheets.Add(Type:=xlWorksheet)
With ws
    .Name = strName
End With

Create a new worksheet - read more How to create a new worksheet

For Each in collection Worksheet

The code below shows how you can loop through a Sheets collection.

Dim wb As Workbook 
Dim ws As Worksheet 
For Each ws In wb.Sheets 

Next ws

Copy Worksheet

Copies the sheet to an open or new workbook.

Dim ws As Worksheets
ws.Copy 

Copy a worksheet - read more How to copy a worksheet

Delete Worksheet

Delete the Worksheet

Dim wss As Worksheet
ws.Delete 

Delete a worksheet - read more Delete worksheet

Activate Worksheet

Makes a sheet the active sheet.

Dim wb As Workbook: Set wb =
Dim ws As Worksheet: Set ws = wb.Sheets("Sheet2")
ws.Activate

Activate a worksheet - read more activate worksheet

Protect Worksheet

Protects a worksheet so that it cannot be modified.

Dim ws As Worksheet 
ws.Protect 

Protect a worksheet - read more activate worksheet

Select Worksheet

Selects the object.

Dim wss As Worksheets 
wss.Select 

Arguments

Replace (used only with sheets). True to replace the current selection with the specified object.False to extend the current selection to include any previously selected objects and the specified object.

Calculate Worksheet

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.

Dim ws As Worksheet 
ws.Calculate 

ChartObjects Worksheet

Returns an object that represents either a single embedded chart (a ChartObject object) or a collection of all the embedded charts (aChartObjects object) on the sheet.

Dim cha As ChartObjects 
Dim ws As Worksheet 
Set cha = ws.ChartObjects()

Arguments

Index The name or number of the chart. This argument can be an array to specify more than one chart.

CheckSpelling Worksheet

Checks the spelling of an object.

Dim ws As Worksheet 
ws.CheckSpelling 

Arguments

CustomDictionary A string that indicates the file name of the custom dictionary to be examined if the word isn't found in the main dictionary. If this argument is omitted the currently specified dictionary is used.
IgnoreUppercase True to have Microsoft Excel ignore words that are all uppercase. Falseto have Microsoft Excel check words that are all uppercase. If this argument is omitted the current setting will be used.
AlwaysSuggest True to have Microsoft Excel display a list of suggested alternate spellings when an incorrect spelling is found. False to have Microsoft Excel wait for you to input the correct spelling. If this argument is omitted the current setting will be used.
SpellLang The language of the dictionary being used. Can be one of theMsoLanguageID values.

CircleInvalid Worksheet

Circles invalid entries on the worksheet.

Dim ws As Worksheet 
ws.CircleInvalid 

ClearArrows Worksheet

Clears the tracer arrows from the worksheet. Tracer arrows are added by using the auditing feature.

Dim ws As Worksheet 
ws.ClearArrows 

ClearCircles Worksheet

Clears circles from invalid entries on the worksheet.

Dim ws As Worksheet 
ws.ClearCircles 

Evaluate Worksheet

Converts a Microsoft Excel name to an object or a value.

Dim var As Variant 
Dim ws As Worksheet 
var = ws.Evaluate(Name:= )

Arguments

Name The name of the object using the naming convention of Microsoft Excel.

ExportAsFixedFormat Worksheet

Exports to a file of the specified format.

Dim ws As Worksheet 
ws.ExportAsFixedFormat Type:= 

Arguments

Type The type of file format to export to.
Filename The file name of the file to be saved. You can include a full path or Excel saves the file in the current folder.
Quality Optional XlFixedFormatQuality. Specifies the quality of the published file.
IncludeDocProperties True to include the document properties; otherwise False.
IgnorePrintAreas True to ignore any print areas set when publishing; otherwise False.
From The number of the page at which to start publishing. If this argument is omitted publishing starts at the beginning.
To The number of the last page to publish. If this argument is omitted publishing ends with the last page.
OpenAfterPublish True to display the file in the viewer after it is published; otherwise False.
FixedFormatExtClassPtr Pointer to the FixedFormatExt class.

Move Worksheet

Moves the sheet to another location in the workbook.

Dim ws As Worksheet 
ws.Move 

Arguments

Before The sheet before which the moved sheet will be placed. You cannot specify Before if you specify After.
After The sheet after which the moved sheet will be placed. You cannot specify After if you specify Before.

OLEObjects Worksheet

Returns an object that represents either a single OLE object (an OLEObject ) or a collection of all OLE objects (an OLEObjectscollection) on the chart or sheet.

Dim obj As Object 
Dim ws As Worksheet 
Set obj = ws.OLEObjects()

Arguments

Index The name or number of the OLE object.

Paste Worksheet

Pastes the contents of the Clipboard onto the sheet.

Dim ws As Worksheet 
ws.Paste 

Arguments

Destination A Range object that specifies where the Clipboard contents should be pasted. If this argument is omitted the current selection is used. This argument can be specified only if the contents of the Clipboard can be pasted into a range. If this argument is specified the Link argument cannot be used.
Link True to establish a link to the source of the pasted data. If this argument is specified the Destination argument cannot be used. The default value is False.

PasteSpecial Worksheet

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.

Dim ws As Worksheet 
ws.PasteSpecial 

Arguments

Format A string that specifies the Clipboard format of the data.
Link True to establish a link to the source of the pasted data. If the source data isn't suitable for linking or the source application doesn't support linking this parameter is ignored. The default value is False.
DisplayAsIcon True to display the pasted as an icon. The default value is False.
IconFileName The name of the file that contains the icon to use if DisplayAsIcon is True.
IconIndex The index number of the icon within the icon file.
IconLabel The text label of the icon.
NoHTMLFormatting True to remove all formatting hyperlinks and images from HTML. False to paste HTML as is. The default value is False.

PivotTables Worksheet

Returns an object that represents either a single PivotTable report (a PivotTable object) or a collection of all the PivotTable reports (aPivotTables object) on a worksheet.

Dim piv As PivotTables 
Dim ws As Worksheet 
Set piv = ws.PivotTables()

Arguments

Index The name or number of the report.

PivotTableWizard Worksheet

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 pvt As PivotTable 
Dim ws As Worksheet 
Set pvt = ws.PivotTableWizard()

Arguments

SourceType An XlPivotTableSourceType value that represents the source of the report data. If you specify this argument you must also specify SourceData. If SourceType and SourceData are omitted Microsoft Excel assumes that the source type is xlDatabase and the source data comes from the named range Database. If this named range doesn't exist Microsoft Excel uses the current region if the current selection is in a range of more than 10 cells that contain data. If this isn't true this method will fail.
SourceData The data for the new report. Can be a Range object an array of ranges or a text constant that represents the name of another report. For an external database SourceData is an array of strings containing the SQL query string where each element is up to 255 characters in length. You should use the Connection argument to specify the ODBC connection string. For compatibility with earlier versions of Excel SourceData can be a two-element array. The first element is the connection string specifying the ODBC source for the data. The second element is the SQL query string used to get the data. If you specify SourceData you must also specifySourceType. If the active cell is inside the SourceData range you must specify TableDestination as well.
TableDestination A Range object specifying where the report should be placed on the worksheet. If this argument is omitted the report is placed at the active cell.
TableName A string that specifies the name of the new report.
RowGrand True to show grand totals for rows in the report.
ColumnGrand True to show grand totals for columns in the report.
SaveData True to save data with the report. False to save only the report definition.
HasAutoFormat True to have Microsoft Excel automatically format the report when it's refreshed or when fields are moved.
AutoPage Valid only if SourceType is xlConsolidation. True to have Microsoft Excel create a page field for the consolidation. If AutoPage is False you must create the page field or fields.
Reserved Not used by Microsoft Excel.
BackgroundQuery True to have Excel perform queries for the report asynchronously (in the background). The default value is False.
OptimizeCache True to optimize the PivotTable cache when it's constructed. The default value is False.
PageFieldOrder The order in which page fields are added to the PivotTable report's layout. Can be one of the following XlOrder constants: xlDownThenOveror xlOverThenDown. The default value is xlDownThenOver.
PageFieldWrapCount The number of page fields in each column or row in the PivotTable report. The default value is 0 (zero).
ReadData True to create a PivotTable cache that contains all records from the external database; this cache can be very large. If ReadData is False you can set some of the fields asserver-based page fields before the data is actually read.
Connection A string that contains ODBC settings that allow Excel to connect to an ODBC data source. The connection string has the form ODBC;connection string. This argument overrides any previous setting for the PivotCache object's Connection property.

PrintOut Worksheet

Prints the object.

Dim ws As Worksheet 
ws.PrintOut 

Arguments

From The number of the page at which to start printing. If this argument is omitted printing starts at the beginning.
To The number of the last page to print. If this argument is omitted printing ends with the last page.
Copies The number of copies to print. If this argument is omitted one copy is printed.
Preview True to have Microsoft Excel invoke print preview before printing the object.False (or omitted) to print the object immediately.
ActivePrinter Sets the name of the active printer.
PrintToFile True to print to a file. If PrToFileName is not specified Microsoft Excel prompts the user to enter the name of the output file.
Collate True to collate multiple copies.
PrToFileName If PrintToFile is set to True this argument specifies the name of the file you want to print to.
IgnorePrintAreas True to ignore print areas and print the entire object.

PrintPreview Worksheet

Shows a preview of the object as it would look when printed.

Dim ws As Worksheet 
ws.PrintPreview 

Arguments

EnableChanges Pass a Boolean value to specify if the user can change the margins and other page setup options available in print preview.

ResetAllPageBreaks Worksheet

Resets all page breaks on the specified worksheet.

Dim ws As Worksheet 
ws.ResetAllPageBreaks 

SaveAs Worksheet

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

Dim ws As Worksheet 
ws.SaveAs Filename:= 

Arguments

Filename Variant. A string that indicates the name of the file to be saved. You can include a full path; if you don't
FileFormat The file format to use when you save the file. For a list of valid choices
Password A case-sensitive string (no more than 15 characters) that indicates the protection password to be given to the file.
WriteResPassword A string that indicates the write-reservation password for this file. If a file is saved with the password and the password isn't supplied when the file is opened
ReadOnlyRecommended True to display a message when the file is opened
CreateBackup True to create a backup file.
AddToMru True to add this workbook to the list of recently used files. The default value is False.
TextCodepage Not used in U.S. English Microsoft Excel.
TextVisualLayout Not used in U.S. English Microsoft Excel.
Local True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).

Scenarios Worksheet

Returns an object that represents either a single scenario (a Scenario object) or a collection of scenarios (a Scenarios object) on the worksheet.

Dim sce As Scenarios 
Dim ws As Worksheet 
Set sce = ws.Scenarios()

Arguments

Index The name or number of the scenario. Use an array to specify more than one scenario.

SetBackgroundPicture Worksheet

Sets the background graphic for a worksheet.

Dim ws As Worksheet 
ws.SetBackgroundPicture Filename:= 

Arguments

Filename The name of the graphic file.

ShowAllData Worksheet

Makes all rows of the currently filtered list visible. If AutoFilter is in use, this method changes the arrows to "All."

Dim ws As Worksheet 
ws.ShowAllData 

ShowDataForm Worksheet

Displays the data form associated with the worksheet.

Dim ws As Worksheet 
ws.ShowDataForm 

Unprotect Worksheet

Removes protection from a sheet or workbook. This method has no effect if the sheet or workbook isn't protected.

Dim ws As Worksheet 
ws.Unprotect 

Arguments

Password A string that denotes the case-sensitive password to use to unprotect the sheet or workbook. If the sheet or workbook isn't protected with a password this argument is ignored. If you omit this argument for a sheet that's protected with a password you'll be prompted for the password. If you omit this argument for a workbook that's protected with a password the method fails.

XmlDataQuery Worksheet

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.

Dim rng As Range 
Dim ws As Worksheet 
Set rng = ws.XmlDataQuery(XPath:= )

Arguments

XPath The XPath to query for.
SelectionNamespaces A space-delimited String that contains the namespaces referenced in the XPath parameter. A run-time error will be generated if one of the specified namespaces cannot be resolved.
Map Specify an XmlMap if you want to query for the XPath within a specific map.

XmlMapQuery Worksheet

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.

Dim rng As Range 
Dim ws As Worksheet 
Set rng = ws.XmlMapQuery(XPath:= )

Arguments

XPath The XPath to query for. A string that specifies a case-sensitive password for the worksheet or workbook. If this argument is omitted you can unprotect the worksheet or workbook without using a password. Otherwise you must specify the password to unprotect the worksheet or workbook. If you forget the password you cannot unprotect the worksheet or workbook. Use strong passwords that combine uppercase and lowercase letters numbers and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. For more information see Help protect your personal information with strong passwords. It is critical that you remember your password. If you forget your password Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.
SelectionNamespaces A space-delimited String that contains the namespaces referenced in the XPath parameter. A run-time error will be generated if one of the specified namespaces cannot be resolved. True to protect shapes. The default value is True.
Map Specify an XML map if you want to query for the XPath within a specific map. True to protect contents. For a chart this protects the entire chart. For a worksheet this protects the locked cells. The default value isTrue.

FillAcrossSheets Worksheet

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

Dim wss As Sheets 
wss.FillAcrossSheets Range:= 

Arguments

Range The range to fill on all the worksheets in the collection. The range must be from a worksheet within the collection. True allows the user to delete columns on the protected worksheet where every cell in the column to be deleted is unlocked. The default value is False.
Type Specifies how to copy the range. True allows the user to delete rows on the protected worksheet where every cell in the row to be deleted is unlocked. The default value is False.

For more info see

Microsoft Office Object reference on Worksheet