{"id":499,"date":"2013-03-03T13:22:51","date_gmt":"2013-03-03T13:22:51","guid":{"rendered":"http:\/\/excelvbatutor.com\/?page_id=499"},"modified":"2020-04-23T10:57:01","modified_gmt":"2020-04-23T10:57:01","slug":"excel-vba-lesson-20-worksheet-object","status":"publish","type":"page","link":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-20-worksheet-object\/","title":{"rendered":"Excel VBA Lesson 20: The Worksheet Object"},"content":{"rendered":"\n<h4 class=\"wp-block-heading\"><strong><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-19-range-object\/\">&lt;&lt;Lesson 19&gt;&gt;<\/a> <a href=\"http:\/\/excelvbatutor.com\/index.php\/tutorial\/\">[Contents] <\/a><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-21-the-workbook-object\/\">&lt;&lt;Lesson 21&gt;&gt;<\/a><\/strong><\/h4>\n\n\n\n<h3 class=\"wp-block-heading\">20.1 The Worksheet Properties in Excel VBA<\/h3>\n\n\n\n<p>Similar to the Range Object, the Worksheet object has its own set of properties and methods. When we write Excel VBA code involving the Worksheet object, we use <i>Worksheets<\/i>. The reason is that we are dealing with a collection of worksheets most of the time, so using Worksheets enables us to manipulate multiple worksheets at the same time.<\/p>\n\n\n\n<p>Some of the common properties of the worksheet are name<i>, count, cells, columns, rows<\/i> and <i>columnWidth. <\/i><br>\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br>\n<ins class=\"adsbygoogle\" style=\"display: block; text-align: center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins><br>\n<script><br \/>\n     (adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><b>Example 20.1<\/b><\/h4>\n\n\n\n<p>Private Sub CommandButton1_Click()<\/p>\n\n\n\n<p>MsgBox Worksheets(1).Name<\/p>\n\n\n\n<p>End Sub<\/p>\n\n\n\n<p>The above example will cause a pop-up dialog that displays the worksheet name as sheet 1, as shown below:<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><a href=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_Figure16.1.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"154\" height=\"141\" src=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_Figure16.1.jpg\" alt=\"vba_Figure16.1\" class=\"wp-image-502\"\/><\/a><\/figure><\/div>\n\n\n\n<p><strong>Figure 16.1<\/strong><\/p>\n\n\n\n<p>The count property returns the number of worksheets in an opened workbook.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><b>Example 20.2<\/b><\/h4>\n\n\n\n<p>Private Sub CommandButton1_Click()<br>\nMsgBox Worksheets.Count<br>\nEnd Sub<\/p>\n\n\n\n<p>The output is shown in Figure 20.2.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><a href=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_Figure16.2.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"154\" height=\"152\" src=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_Figure16.2.jpg\" alt=\"vba_Figure16.2\" class=\"wp-image-504\"\/><\/a><\/figure><\/div>\n\n\n\n<p><strong>Figure 20.2<\/strong><\/p>\n\n\n\n<p><script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br>\n<ins class=\"adsbygoogle\" style=\"display: block; text-align: center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins><br>\n<script><br \/>\n     (adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><b>Example 20.3 <\/b><\/h4>\n\n\n\n<p>The count property in this example will return the number of columns in the worksheet.<\/p>\n\n\n\n<p>Private Sub CommandButton1_Click()<\/p>\n\n\n\n<p>MsgBox Worksheets(1).Columns.Count<\/p>\n\n\n\n<p>End Sub<\/p>\n\n\n\n<p>The output is shown below:<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><a href=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_Figure16.3.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"154\" height=\"152\" src=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_Figure16.3.jpg\" alt=\"vba_Figure16.3\" class=\"wp-image-506\"\/><\/a><\/figure><\/div>\n\n\n\n<p>&nbsp;<strong>Figure 20.3<\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Example 20.4<\/strong><\/h3>\n\n\n\n<p>The count property in this example will return the number of rows in the worksheet.<\/p>\n\n\n\n<p>Private Sub CommandButton1_Click()<\/p>\n\n\n\n<p>MsgBox Worksheets(1).Rows.Count<\/p>\n\n\n\n<p>End Sub<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><a href=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_Figure16.4.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"154\" height=\"152\" src=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_Figure16.4.jpg\" alt=\"vba_Figure16.4\" class=\"wp-image-508\"\/><\/a><\/figure><\/div>\n\n\n\n<p>&nbsp;<strong>Figure 20.4<\/strong><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><b>20.2 The Worksheet Methods<\/b><\/h4>\n\n\n\n<p>Some of the worksheet methods are <i>add, delete, select, SaveAs, copy, paste<\/i> and more.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><b>&nbsp;<\/b><b>Example 20.5<\/b><\/h4>\n\n\n\n<p>In this example, when the user clicks the first command button, it will add a new sheet to the workbook. When the user clicks the second command button, it will delete the new worksheet that has been added earlier.<\/p>\n\n\n\n<p>Private Sub CommandButton1_Click()<\/p>\n\n\n\n<p>Worksheets. Add<\/p>\n\n\n\n<p>End Sub<\/p>\n\n\n\n<p>Private Sub CommandButton2_Click()<\/p>\n\n\n\n<p>Worksheets(1).Delete<\/p>\n\n\n\n<p>End Sub<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><b>Example 20.6<\/b><\/h4>\n\n\n\n<p>The select method associated with worksheet lets the user select a particular worksheet. In this example, worksheet 2 will be selected.<\/p>\n\n\n\n<p>Private Sub CommandButton1_Click()<\/p>\n\n\n\n<p>\u2018Worksheet 2 will be selected<\/p>\n\n\n\n<p>Worksheets(2).Select<\/p>\n\n\n\n<p>End Sub<\/p>\n\n\n\n<p>The select method can also be used together with the Worksheet\u2019s properties Cells, Columns and Rows as shown in the following examples.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><b>Example 20.7<\/b><\/h4>\n\n\n\n<p>Private Sub CommandButton1_Click()<\/p>\n\n\n\n<p>\u2018Cell A1 will be selected<\/p>\n\n\n\n<p>Worksheets (1).Cells (1).Select<\/p>\n\n\n\n<p>End Sub<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><b>Example 20.8<\/b><\/h4>\n\n\n\n<p>Private Sub CommandButton1_Click()<\/p>\n\n\n\n<p>\u2018Column 1 will be selected<\/p>\n\n\n\n<p>Worksheets (1).Columns (1).Select<\/p>\n\n\n\n<p>End Sub<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><b>Example 20.9<\/b><\/h4>\n\n\n\n<p>Private Sub CommandButton1_Click()<\/p>\n\n\n\n<p>\u2018Row 1 will be selected<\/p>\n\n\n\n<p>Worksheets (1).Rows (1).Select<\/p>\n\n\n\n<p>End Sub<\/p>\n\n\n\n<p>Excel VBA also allows us to write code for copy and paste. Let\u2019s look at the following Example:<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><b>&nbsp;<\/b><b>Example 20.10<\/b><\/h4>\n\n\n\n<p>Private Sub CommandButton1_Click()<\/p>\n\n\n\n<p>\u2018To copy the content of a cell 1<\/p>\n\n\n\n<p>Worksheets(1).Cells(1).Select<\/p>\n\n\n\n<p>Selection.Copy<\/p>\n\n\n\n<p>End Sub<\/p>\n\n\n\n<p>Private Sub CommandButton2_Click()<\/p>\n\n\n\n<p>\u2018To paste the content of cell 1 to cell 2<\/p>\n\n\n\n<p>Worksheets(1).Cells(2).Select<\/p>\n\n\n\n<p>ActiveSheet.Paste<br>\nEnd Sub<\/p>\n\n\n\n<p><script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br>\n<ins class=\"adsbygoogle\" style=\"display: block; text-align: center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins><br>\n<script><br \/>\n     (adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><br>\n<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-19-range-object\/\">&lt;&lt;Lesson 19&gt;&gt;<\/a> <a href=\"http:\/\/excelvbatutor.com\/index.php\/tutorial\/\">[Contents] <\/a><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-21-the-workbook-object\/\">&lt;&lt;Lesson 21&gt;&gt;<\/a><\/strong><\/h4>\n","protected":false},"excerpt":{"rendered":"<p>&lt;&lt;Lesson 19&gt;&gt; [Contents] &lt;&lt;Lesson 21&gt;&gt; 20.1 The Worksheet Properties in Excel VBA Similar to the Range Object, the Worksheet object has its own set of properties and methods. When we write Excel VBA code involving the Worksheet object, we use Worksheets. The reason is that we are dealing with a collection of worksheets most of &hellip; <a href=\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-20-worksheet-object\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Excel VBA Lesson 20: The Worksheet Object&#8221;<\/span><\/a><\/p>\n","protected":false},"author":5012,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"categories":[10],"tags":[],"class_list":["post-499","page","type-page","status-publish","hentry","category-object"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.1.1 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Excel VBA Lesson 20: The Worksheet Object - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor<\/title>\n<meta name=\"description\" content=\"This Excel VBA lesson illustrates the usage of properties and methods associated with the Worksheet object\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/excelvbatutor.com\/vba_lesson20.htm\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel VBA Lesson 20: The Worksheet Object - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\" \/>\n<meta property=\"og:description\" content=\"This Excel VBA lesson illustrates the usage of properties and methods associated with the Worksheet object\" \/>\n<meta property=\"og:url\" content=\"https:\/\/excelvbatutor.com\/vba_lesson20.htm\" \/>\n<meta property=\"og:site_name\" content=\"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\" \/>\n<meta property=\"article:modified_time\" content=\"2020-04-23T10:57:01+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_Figure16.1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"154\" \/>\n\t<meta property=\"og:image:height\" content=\"141\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-20-worksheet-object\/\",\"url\":\"https:\/\/excelvbatutor.com\/vba_lesson20.htm\",\"name\":\"Excel VBA Lesson 20: The Worksheet Object - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\",\"isPartOf\":{\"@id\":\"https:\/\/excelvbatutor.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson20.htm#primaryimage\"},\"image\":{\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson20.htm#primaryimage\"},\"thumbnailUrl\":\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_Figure16.1.jpg\",\"datePublished\":\"2013-03-03T13:22:51+00:00\",\"dateModified\":\"2020-04-23T10:57:01+00:00\",\"description\":\"This Excel VBA lesson illustrates the usage of properties and methods associated with the Worksheet object\",\"breadcrumb\":{\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson20.htm#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/excelvbatutor.com\/vba_lesson20.htm\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson20.htm#primaryimage\",\"url\":\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_Figure16.1.jpg\",\"contentUrl\":\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_Figure16.1.jpg\",\"width\":154,\"height\":141},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson20.htm#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/excelvbatutor.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel VBA Lesson 20: The Worksheet Object\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/excelvbatutor.com\/#website\",\"url\":\"https:\/\/excelvbatutor.com\/\",\"name\":\"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\",\"description\":\"Master Excel VBA with free tutorials, examples, and personalized guidance. Perfect for beginners and advanced users looking to automate Excel.\",\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Excel VBA Lesson 20: The Worksheet Object - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","description":"This Excel VBA lesson illustrates the usage of properties and methods associated with the Worksheet object","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/excelvbatutor.com\/vba_lesson20.htm","og_locale":"en_US","og_type":"article","og_title":"Excel VBA Lesson 20: The Worksheet Object - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","og_description":"This Excel VBA lesson illustrates the usage of properties and methods associated with the Worksheet object","og_url":"https:\/\/excelvbatutor.com\/vba_lesson20.htm","og_site_name":"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","article_modified_time":"2020-04-23T10:57:01+00:00","og_image":[{"width":154,"height":141,"url":"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_Figure16.1.jpg","type":"image\/jpeg"}],"twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-20-worksheet-object\/","url":"https:\/\/excelvbatutor.com\/vba_lesson20.htm","name":"Excel VBA Lesson 20: The Worksheet Object - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","isPartOf":{"@id":"https:\/\/excelvbatutor.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/excelvbatutor.com\/vba_lesson20.htm#primaryimage"},"image":{"@id":"https:\/\/excelvbatutor.com\/vba_lesson20.htm#primaryimage"},"thumbnailUrl":"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_Figure16.1.jpg","datePublished":"2013-03-03T13:22:51+00:00","dateModified":"2020-04-23T10:57:01+00:00","description":"This Excel VBA lesson illustrates the usage of properties and methods associated with the Worksheet object","breadcrumb":{"@id":"https:\/\/excelvbatutor.com\/vba_lesson20.htm#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/excelvbatutor.com\/vba_lesson20.htm"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/excelvbatutor.com\/vba_lesson20.htm#primaryimage","url":"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_Figure16.1.jpg","contentUrl":"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/03\/vba_Figure16.1.jpg","width":154,"height":141},{"@type":"BreadcrumbList","@id":"https:\/\/excelvbatutor.com\/vba_lesson20.htm#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/excelvbatutor.com\/"},{"@type":"ListItem","position":2,"name":"Excel VBA Lesson 20: The Worksheet Object"}]},{"@type":"WebSite","@id":"https:\/\/excelvbatutor.com\/#website","url":"https:\/\/excelvbatutor.com\/","name":"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","description":"Master Excel VBA with free tutorials, examples, and personalized guidance. Perfect for beginners and advanced users looking to automate Excel.","inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/499","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/users\/5012"}],"replies":[{"embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/comments?post=499"}],"version-history":[{"count":38,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/499\/revisions"}],"predecessor-version":[{"id":3464,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/499\/revisions\/3464"}],"wp:attachment":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/media?parent=499"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/categories?post=499"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/tags?post=499"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}