{"id":12177,"date":"2013-06-18T15:09:53","date_gmt":"2013-06-18T15:09:53","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=3035"},"modified":"2023-05-21T17:55:29","modified_gmt":"2023-05-21T17:55:29","slug":"few-very-useful-and-small-macros","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/","title":{"rendered":"5 very useful and small macros"},"content":{"rendered":"

In the first part of this article, I am going to share very small but useful VBA codes which are most frequently used in day-to-day VBA programming. Many of you had requested them so I have clubbed all of them together and made one article \ud83d\ude42 Enjoy reading this.<\/p>\n

\n

Index<\/h2>\n
\n
    \n
  1. Closing All Open Workbooks<\/a><\/li>\n
  2. Closing All Inactive Workbooks<\/a><\/li>\n
  3. Saving All Open Workbooks<\/a><\/li>\n
  4. Get the name of the First Sheet in a Workbook<\/a><\/li>\n
  5. Get the name of the Last Sheet in a Workbook<\/a><\/li>\n<\/ol>\n<\/div>\n

    Closing All Open Workbooks<\/h1>\n

    You can call the below function to close all open workbooks:<\/p>\n

    \r\n\r\nPublic Sub CloseAllWorkbooks()\r\nDim Wb As Workbook\r\n' Workbooks is the collection of all open workbooks\r\nFor Each Wb In Workbooks\r\n    If Wb.Name <> ThisWorkbook.Name Then\r\n        Wb.Close savechanges:=True 'Set False if you want them to close without saving it\r\n    End If\r\nNext Wb\r\n'  This statement will close the current workbook \r\n'  where vba code is running. Below stattement can \r\n'  not be written before\r\nThisWorkbook.Close savechanges:=True\r\n\r\nEnd Sub\r\n\r\n<\/code><\/pre>\n
    Closing All Inactive Workbooks<\/h1>\n
    You can call the below function to close all inactive workbooks:<\/p>\n
    \r\n\r\nPublic Sub CloseAllInactiveWorkbooks()\r\nDim Wb As Workbook\r\nDim WbName As String\r\nWbName = ActiveWorkbook.Name\r\n ' Workbooks is the collection of all open Workbooks\r\nFor Each Wb In Workbooks\r\n    If Wb.Name <> WbName Then\r\n        Wb.Close savechanges:=True\r\n    End If\r\nNext Wb\r\n\r\nEnd Sub\r\n\r\n<\/code><\/pre>\n
    Saving All Open Workbooks<\/h1>\n
    Below code will save all the open workbooks:
    \n <\/p>\n
    \r\nPublic Sub SaveAllWorkbook()\r\nDim WB As Workbook\r\nFor Each WB In Workbooks\r\n    WB.Save\r\nNext WB\r\nEnd Sub\r\n\r\n<\/code><\/pre>\n
    Get the name of First Sheet in a Workbook<\/h1>\n
    Keyword  Sheets<\/strong> is a collection of all Sheets and WorkSheets of a workbook. Each sheet can be referred by passing the index number. <\/p>\n
    For example:<\/strong> First sheet can be referred as Sheets(1), Second as Sheets(2) and so on…<\/p>\n
    Below code will give you name of the first sheet in your workbook. <\/p>\n
    \r\nPublic Sub FirstSheetName()\r\n  Dim firstSheet As String\r\n  firstSheetName = Sheets(1).Name\r\nEnd Sub\r\n\r\n<\/code><\/pre>\n
    Get the name of Last Sheet in a Workbook<\/h1>\n
    Below code will give you name of the last sheet in your workbook. <\/p>\n
    Note: Count is the property of Object Sheets which returns the total number of sheets available in the workbook<\/i><\/span><\/p>\n
    \r\nPublic Sub LastSheetName()\r\n  Dim firstSheet As String\r\n  firstSheetName = Sheets(Sheets.Count).Name\r\nEnd Sub\r\n\r\n<\/code><\/pre>\n<\/span>","protected":false},"excerpt":{"rendered":"
    In the first part of this article, I am going to share very small but useful VBA codes which are most frequently used in day-to-day VBA programming. Many of you had requested them so I have clubbed all of them together and made one article \ud83d\ude42 Enjoy reading this. Index Closing All Open Workbooks Closing […]<\/p>\n","protected":false},"author":45,"featured_media":242690,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1679,1676,1678,1682],"tags":[],"class_list":["post-12177","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-macro-beginner","category-excel-tips","category-interesting-vba-functions","category-popular-articles"],"yoast_head":"\n5 very useful and small macros - Let's excel in Excel<\/title>\n<meta name=\"description\" content=\"5 very useful and small macros. Closing All Open Workbooks. Closing All Inactive Workbook. Saving All Open Workbooks. Get the name of the First Sheet\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"5 very useful and small macros\" \/>\n<meta property=\"og:description\" content=\"5 very useful and small macros. Closing All Open Workbooks. Closing All Inactive Workbook. Saving All Open Workbooks. Get the name of the First Sheet\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2013-06-18T15:09:53+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-05-21T17:55:29+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/06\/5-useful-macros.png\" \/>\n\t<meta property=\"og:image:width\" content=\"400\" \/>\n\t<meta property=\"og:image:height\" content=\"250\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"5 very useful and small macros\",\"datePublished\":\"2013-06-18T15:09:53+00:00\",\"dateModified\":\"2023-05-21T17:55:29+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/\"},\"wordCount\":230,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/06\/5-useful-macros.png\",\"articleSection\":[\"Excel Macro Beginner\",\"Excel Tips\",\"Interesting VBA Functions\",\"Popular Articles\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/\",\"name\":\"5 very useful and small macros - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/06\/5-useful-macros.png\",\"datePublished\":\"2013-06-18T15:09:53+00:00\",\"dateModified\":\"2023-05-21T17:55:29+00:00\",\"description\":\"5 very useful and small macros. Closing All Open Workbooks. Closing All Inactive Workbook. Saving All Open Workbooks. Get the name of the First Sheet\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/#primaryimage\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/06\/5-useful-macros.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/06\/5-useful-macros.png\",\"width\":400,\"height\":250,\"caption\":\"5 Useful VBA Codes\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Macro Beginner\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-macro-beginner\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"5 very useful and small macros\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"5 very useful and small macros - Let's excel in Excel","description":"5 very useful and small macros. Closing All Open Workbooks. Closing All Inactive Workbook. Saving All Open Workbooks. Get the name of the First Sheet","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:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/","og_locale":"en_US","og_type":"article","og_title":"5 very useful and small macros","og_description":"5 very useful and small macros. Closing All Open Workbooks. Closing All Inactive Workbook. Saving All Open Workbooks. Get the name of the First Sheet","og_url":"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2013-06-18T15:09:53+00:00","article_modified_time":"2023-05-21T17:55:29+00:00","og_image":[{"width":400,"height":250,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/06\/5-useful-macros.png","type":"image\/png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"5 very useful and small macros","datePublished":"2013-06-18T15:09:53+00:00","dateModified":"2023-05-21T17:55:29+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/"},"wordCount":230,"commentCount":0,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/06\/5-useful-macros.png","articleSection":["Excel Macro Beginner","Excel Tips","Interesting VBA Functions","Popular Articles"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/","url":"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/","name":"5 very useful and small macros - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/#primaryimage"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/06\/5-useful-macros.png","datePublished":"2013-06-18T15:09:53+00:00","dateModified":"2023-05-21T17:55:29+00:00","description":"5 very useful and small macros. Closing All Open Workbooks. Closing All Inactive Workbook. Saving All Open Workbooks. Get the name of the First Sheet","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/#primaryimage","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/06\/5-useful-macros.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2013\/06\/5-useful-macros.png","width":400,"height":250,"caption":"5 Useful VBA Codes"},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2013\/06\/few-very-useful-and-small-macros\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Macro Beginner","item":"https:\/\/vmlogger.com\/excel\/excel-macro-beginner\/"},{"@type":"ListItem","position":3,"name":"5 very useful and small macros"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12177"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=12177"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12177\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media\/242690"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12177"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12177"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12177"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}