How to use Excel class Worksheet
Worksheet represents a worksheet. This page provides code for the methods of the Excel class Worksheet: Add, Copy, Delete, Activate, Select, Calculate, ChartObjects, CheckSpelling, CircleInvalid, ClearArrows, ClearCircles, Evaluate, ExportAsFixedFormat, Move, OLEObjects, Paste, PasteSpecial, PivotTables, PivotTableWizard, PrintOut, PrintPreview, Protect, ResetAllPageBreaks, SaveAs, Scenarios, SetBackgroundPicture, ShowAllData, ShowDataForm, Unprotect, XmlDataQuery, XmlMapQuery, FillAcrossSheets.
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
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
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
Copies the sheet to an open or new workbook.
Dim ws As Worksheets ws.Copy
Delete the Worksheet
Dim wss As Worksheet ws.Delete
Makes a sheet the active sheet.
Dim wb As Workbook: Set wb = Dim ws As Worksheet: Set ws = wb.Sheets("Sheet2") ws.Activate
Protects a worksheet so that it cannot be modified.
Dim ws As Worksheet ws.Protect
Selects the object.
Dim wss As Worksheets wss.Select
|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.|
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
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()
|Index||The name or number of the chart. This argument can be an array to specify more than one chart.|
Checks the spelling of an object.
Dim ws As Worksheet ws.CheckSpelling
|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.|
Circles invalid entries on the worksheet.
Dim ws As Worksheet ws.CircleInvalid
Clears the tracer arrows from the worksheet. Tracer arrows are added by using the auditing feature.
Dim ws As Worksheet ws.ClearArrows
Clears circles from invalid entries on the worksheet.
Dim ws As Worksheet ws.ClearCircles
Converts a Microsoft Excel name to an object or a value.
Dim var As Variant Dim ws As Worksheet var = ws.Evaluate(Name:= )
|Name||The name of the object using the naming convention of Microsoft Excel.|
Exports to a file of the specified format.
Dim ws As Worksheet ws.ExportAsFixedFormat Type:=
|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.|
Moves the sheet to another location in the workbook.
Dim ws As Worksheet ws.Move
|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.|
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()
|Index||The name or number of the OLE object.|
Pastes the contents of the Clipboard onto the sheet.
Dim ws As Worksheet ws.Paste
|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.|
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
|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.|
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()
|Index||The name or number of the report.|
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()
|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.|
Prints the object.
Dim ws As Worksheet ws.PrintOut
|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.|
Shows a preview of the object as it would look when printed.
Dim ws As Worksheet ws.PrintPreview
|EnableChanges||Pass a Boolean value to specify if the user can change the margins and other page setup options available in print preview.|
Resets all page breaks on the specified worksheet.
Dim ws As Worksheet ws.ResetAllPageBreaks
Saves changes to the chart or worksheet in a different file.
Dim ws As Worksheet ws.SaveAs Filename:=
|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).|
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()
|Index||The name or number of the scenario. Use an array to specify more than one scenario.|
Sets the background graphic for a worksheet.
Dim ws As Worksheet ws.SetBackgroundPicture Filename:=
|Filename||The name of the graphic file.|
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
Displays the data form associated with the worksheet.
Dim ws As Worksheet ws.ShowDataForm
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
|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.|
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:= )
|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.|
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:= )
|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.|
Copies a range to the same area on all other worksheets in a collection.
Dim wss As Sheets wss.FillAcrossSheets Range:=
|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.|