{"id":17234,"date":"2022-10-23T14:29:36","date_gmt":"2022-10-23T08:59:36","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/"},"modified":"2022-10-23T14:29:36","modified_gmt":"2022-10-23T08:59:36","slug":"solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/","title":{"rendered":"[Solved] Change VBA code to find and replace in multiple worksheets [duplicate]"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-68504714\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"68504714\" data-parentid=\"68500027\" data-score=\"0\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>Use the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/learn.microsoft.com\/en-us\/office\/vba\/api\/excel.workbook.sheets\">sheets<\/a> property of the workbook to return a collection that you can loop through with <code>For Each<\/code>. Skip over the <code>Names<\/code> sheet and avoid reading the <code>names<\/code> sheet multiple times by putting the values into an array.<\/p>\n<pre class=\"lang-vb prettyprint-override\"><code>Option Explicit\nSub multiFindandReplace()\n    Dim wb As Workbook, ws As Worksheet\n    Dim myList As Variant, myRange As Range\n    Dim i As Long, msg As String\n     \n    ' find\/replace list\n    Set wb = ThisWorkbook ' or ActiveWorkbook\n    myList = wb.Sheets(\"Names\").Range(\"A1:B238\").Value2\n    \n    For Each ws In wb.Sheets\n        If ws.Name &lt;&gt; \"Names\" Then\n            Set myRange = ws.Range(\"A1:Y99\")\n            For i = LBound(myList) To UBound(myList)\n                If Len(myList(i, 1)) &gt; 0 Then\n                    myRange.Replace _\n                      What:=myList(i, 1), _\n                      Replacement:=myList(i, 2), _\n                      LookAt:=xlWhole\n                End If\n            Next i\n            msg = msg &amp; vbCr &amp; ws.Name\n        End If\n    Next\n    MsgBox \"Sheets processed :\" &amp; msg, vbInformation, wb.Name\nEnd Sub\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\"><\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved Change VBA code to find and replace in multiple worksheets [duplicate] <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Use the sheets property of the workbook to return a collection that you can loop through with For Each. Skip over the Names sheet and avoid reading the names sheet multiple times by putting the values into an array. Option Explicit Sub multiFindandReplace() Dim wb As Workbook, ws As Worksheet Dim myList As Variant, &#8230; <a title=\"[Solved] Change VBA code to find and replace in multiple worksheets [duplicate]\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/\" aria-label=\"More on [Solved] Change VBA code to find and replace in multiple worksheets [duplicate]\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[400,401],"class_list":["post-17234","post","type-post","status-publish","format-standard","hentry","category-solved","tag-excel","tag-vba"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] Change VBA code to find and replace in multiple worksheets [duplicate] - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Change VBA code to find and replace in multiple worksheets [duplicate] - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Use the sheets property of the workbook to return a collection that you can loop through with For Each. Skip over the Names sheet and avoid reading the names sheet multiple times by putting the values into an array. Option Explicit Sub multiFindandReplace() Dim wb As Workbook, ws As Worksheet Dim myList As Variant, ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-23T08:59:36+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Change VBA code to find and replace in multiple worksheets [duplicate]\",\"datePublished\":\"2022-10-23T08:59:36+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/\"},\"wordCount\":61,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"excel\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/\",\"name\":\"[Solved] Change VBA code to find and replace in multiple worksheets [duplicate] - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-10-23T08:59:36+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Change VBA code to find and replace in multiple worksheets [duplicate]\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Change VBA code to find and replace in multiple worksheets [duplicate] - JassWeb","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:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Change VBA code to find and replace in multiple worksheets [duplicate] - JassWeb","og_description":"[ad_1] Use the sheets property of the workbook to return a collection that you can loop through with For Each. Skip over the Names sheet and avoid reading the names sheet multiple times by putting the values into an array. Option Explicit Sub multiFindandReplace() Dim wb As Workbook, ws As Worksheet Dim myList As Variant, ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/","og_site_name":"JassWeb","article_published_time":"2022-10-23T08:59:36+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Change VBA code to find and replace in multiple worksheets [duplicate]","datePublished":"2022-10-23T08:59:36+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/"},"wordCount":61,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["excel","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/","url":"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/","name":"[Solved] Change VBA code to find and replace in multiple worksheets [duplicate] - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-10-23T08:59:36+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-change-vba-code-to-find-and-replace-in-multiple-worksheets-duplicate\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Change VBA code to find and replace in multiple worksheets [duplicate]"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/17234","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=17234"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/17234\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=17234"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=17234"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=17234"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}