{"id":11231,"date":"2022-09-26T15:43:23","date_gmt":"2022-09-26T10:13:23","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/"},"modified":"2022-09-26T15:43:23","modified_gmt":"2022-09-26T10:13:23","slug":"solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/","title":{"rendered":"[Solved] How to split a spreadsheet into multiple new spreadsheets each containing a subset of the original data?"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-21904591\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"21904591\" data-parentid=\"21893913\" data-score=\"11\" 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>Try this code. I&#8217;ve commented it in details. But if you have some quesions, ask in comments:). Code saves new wokrbooks in the folder where your current workbook is saved.<\/p>\n<pre><code>Sub test()\n    Dim names As New Collection\n    Dim ws As Worksheet, ws1 As Worksheet\n    Dim wb As Workbook\n    Dim lastrow As Long\n    Dim cell As Range\n    Dim nm As Variant\n    Dim res As Range\n    Dim rngHeader As Range\n\n    Application.ScreenUpdating = False\n    Application.DisplayAlerts = False\n\n    Set ws = ThisWorkbook.Worksheets(\"Sheet1\")\n    With ws\n        'change \"A\" to column with \"Names\"\n        lastrow = .Cells(.Rows.Count, \"A\").End(xlUp).Row\n\n        'change \"A\" to column with \"Names\"\n        For Each cell In .Range(\"A2:A\" &amp; lastrow)\n            On Error Resume Next\n            'collect unique names\n            names.Add CStr(cell.Value), CStr(cell.Value)\n            On Error GoTo 0\n        Next cell\n\n        'disable all filters\n        .AutoFilterMode = False\n\n        'change \"A1:C1\" to headers address of your table\n        Set rngHeader = .Range(\"A1:C1\")\n\n        For Each nm In names\n            With rngHeader\n                'Apply filter to \"Name\" column\n                .AutoFilter Field:=1, Criteria1:=nm\n                On Error Resume Next\n                'get all visible rows \n                Set res = .Offset(2).Resize(lastrow - 1).SpecialCells(xlCellTypeVisible)\n                On Error GoTo 0\n\n                'if there is visible rows, create new WB\n                If Not res Is Nothing Then\n                    'create new workbook\n                    Set wb = Workbooks.Add\n                    'add sheet with name form column \"Names\" (\"Paul\", \"Nick\" or etc)\n                    wb.Worksheets.Add.name = nm\n                    'delete other sheets from new wb\n                    For Each ws1 In wb.Worksheets\n                        If ws1.name &lt;&gt; nm Then ws1.Delete\n                    Next\n\n                    'copy\/paste data\n                    With wb.Worksheets(nm)\n                        'copy headers\n                        .Range(\"A1\").Resize(, rngHeader.Columns.Count).Value = rngHeader.Value\n                        'copy data\n                        .Range(\"A2\").Resize(res.Rows.Count, res.Columns.Count).Value = res.Value\n                    End With\n\n                    'save wb\n                    wb.Close saveChanges:=True, Filename:=ThisWorkbook.Path &amp; \"\\Spreadsheet_\" &amp; nm &amp; \".xlsx\"\n                    Set wb = Nothing\n                End If\n            End With\n        Next\n        'disable all filters\n        .AutoFilterMode = False\n    End With\n\n    Set names = Nothing\n\n    Application.DisplayAlerts = True\n    Application.ScreenUpdating = True\nEnd Sub\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">3<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved How to split a spreadsheet into multiple new spreadsheets each containing a subset of the original data? <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Try this code. I&#8217;ve commented it in details. But if you have some quesions, ask in comments:). Code saves new wokrbooks in the folder where your current workbook is saved. Sub test() Dim names As New Collection Dim ws As Worksheet, ws1 As Worksheet Dim wb As Workbook Dim lastrow As Long Dim cell &#8230; <a title=\"[Solved] How to split a spreadsheet into multiple new spreadsheets each containing a subset of the original data?\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/\" aria-label=\"More on [Solved] How to split a spreadsheet into multiple new spreadsheets each containing a subset of the original data?\">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-11231","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] How to split a spreadsheet into multiple new spreadsheets each containing a subset of the original data? - 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-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] How to split a spreadsheet into multiple new spreadsheets each containing a subset of the original data? - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Try this code. I&#8217;ve commented it in details. But if you have some quesions, ask in comments:). Code saves new wokrbooks in the folder where your current workbook is saved. Sub test() Dim names As New Collection Dim ws As Worksheet, ws1 As Worksheet Dim wb As Workbook Dim lastrow As Long Dim cell ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-26T10:13:23+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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] How to split a spreadsheet into multiple new spreadsheets each containing a subset of the original data?\",\"datePublished\":\"2022-09-26T10:13:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/\"},\"wordCount\":69,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"excel\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/\",\"name\":\"[Solved] How to split a spreadsheet into multiple new spreadsheets each containing a subset of the original data? - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-09-26T10:13:23+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] How to split a spreadsheet into multiple new spreadsheets each containing a subset of the original data?\"}]},{\"@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=1775193939\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] How to split a spreadsheet into multiple new spreadsheets each containing a subset of the original data? - 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-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] How to split a spreadsheet into multiple new spreadsheets each containing a subset of the original data? - JassWeb","og_description":"[ad_1] Try this code. I&#8217;ve commented it in details. But if you have some quesions, ask in comments:). Code saves new wokrbooks in the folder where your current workbook is saved. Sub test() Dim names As New Collection Dim ws As Worksheet, ws1 As Worksheet Dim wb As Workbook Dim lastrow As Long Dim cell ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/","og_site_name":"JassWeb","article_published_time":"2022-09-26T10:13:23+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] How to split a spreadsheet into multiple new spreadsheets each containing a subset of the original data?","datePublished":"2022-09-26T10:13:23+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/"},"wordCount":69,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["excel","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/","url":"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/","name":"[Solved] How to split a spreadsheet into multiple new spreadsheets each containing a subset of the original data? - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-09-26T10:13:23+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-how-to-split-a-spreadsheet-into-multiple-new-spreadsheets-each-containing-a-subset-of-the-original-data\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] How to split a spreadsheet into multiple new spreadsheets each containing a subset of the original data?"}]},{"@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=1775193939","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939","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\/11231","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=11231"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/11231\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=11231"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=11231"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=11231"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}