{"id":16214,"date":"2022-10-14T17:21:23","date_gmt":"2022-10-14T11:51:23","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/"},"modified":"2022-10-14T17:21:23","modified_gmt":"2022-10-14T11:51:23","slug":"solved-excel-vba-filter-range-by-each-unique-value-and-copy-data","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/","title":{"rendered":"[Solved] Excel Vba Filter Range By Each Unique Value and Copy Data"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-50539973\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"50539973\" data-parentid=\"50538315\" 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>Create a list of all the vendors in a separate place and remove duplicates.<br \/>\nThen use this list as an array to filter for individual vendors. Then you can put the data for each vendor in a separate sheet. Here&#8217;s the code for this, assuming the data you provided above is put in the cells G1:H6.<\/p>\n<pre><code>Sub Macro2()\n\n    Range(Selection, Selection.End(xlDown)).Select\n    Selection.Copy\n    Range(\"K1\").Select\n    ActiveSheet.Paste\n    Application.CutCopyMode = False\n    Selection.RemoveDuplicates Columns:=1, Header:=xlNo\n\n    Dim num_vendors\n    num_vendors = Selection.SpecialCells(xlCellTypeConstants).Count - 1\n    Range(\"G1\").Select\n    Range(Selection, Selection.End(xlToRight)).Select\n    Range(Selection, Selection.End(xlDown)).Select\n    Selection.AutoFilter\n\nFor i = 1 To num_vendors\n    Range(\"G1\").Select\n    Range(Selection, Selection.End(xlToRight)).Select\n    Range(Selection, Selection.End(xlDown)).Select\n    Selection.AutoFilter Field:=1, Criteria1:=Cells(i + 1, 11).Value\n\n    Range(\"G1\").Select\n    Range(Selection, Selection.End(xlToRight)).Select\n    Range(Selection, Selection.End(xlDown)).Select\n    Selection.SpecialCells(xlCellTypeVisible).Select\n    Selection.Copy\n    Sheets.Add After:=Sheets(ThisWorkbook.Sheets.Count)\n    Sheets(i + 1).Select\n    ActiveSheet.Paste\n    Sheets(1).Select\nNext i\n\nApplication.CutCopyMode = False\n\nEnd Sub\n<\/code><\/pre>\n<p>The first half of the code copies the vendor list and pastes it in K1. Then removes duplicates, determines the number of vendors from the remaining list, then goes back to G1 and enables filtering.<\/p>\n<p>The second half of the code uses a for loop to filter the table for each vendor one by one, copy the visible cells, insert a sheet at the end of all sheets, switch to that sheet, paste the copied data and then go back to the original sheet.<\/p>\n<p>This code should work regardless of the number of vendors of the number of rows for each vendor.<\/p>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">1<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved Excel Vba Filter Range By Each Unique Value and Copy Data <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Create a list of all the vendors in a separate place and remove duplicates. Then use this list as an array to filter for individual vendors. Then you can put the data for each vendor in a separate sheet. Here&#8217;s the code for this, assuming the data you provided above is put in the &#8230; <a title=\"[Solved] Excel Vba Filter Range By Each Unique Value and Copy Data\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/\" aria-label=\"More on [Solved] Excel Vba Filter Range By Each Unique Value and Copy 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-16214","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] Excel Vba Filter Range By Each Unique Value and Copy 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-excel-vba-filter-range-by-each-unique-value-and-copy-data\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Excel Vba Filter Range By Each Unique Value and Copy Data - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Create a list of all the vendors in a separate place and remove duplicates. Then use this list as an array to filter for individual vendors. Then you can put the data for each vendor in a separate sheet. Here&#8217;s the code for this, assuming the data you provided above is put in the ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-14T11:51: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=\"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-excel-vba-filter-range-by-each-unique-value-and-copy-data\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Excel Vba Filter Range By Each Unique Value and Copy Data\",\"datePublished\":\"2022-10-14T11:51:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/\"},\"wordCount\":186,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"excel\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/\",\"name\":\"[Solved] Excel Vba Filter Range By Each Unique Value and Copy Data - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-10-14T11:51:23+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Excel Vba Filter Range By Each Unique Value and Copy 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=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] Excel Vba Filter Range By Each Unique Value and Copy 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-excel-vba-filter-range-by-each-unique-value-and-copy-data\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Excel Vba Filter Range By Each Unique Value and Copy Data - JassWeb","og_description":"[ad_1] Create a list of all the vendors in a separate place and remove duplicates. Then use this list as an array to filter for individual vendors. Then you can put the data for each vendor in a separate sheet. Here&#8217;s the code for this, assuming the data you provided above is put in the ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/","og_site_name":"JassWeb","article_published_time":"2022-10-14T11:51:23+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-excel-vba-filter-range-by-each-unique-value-and-copy-data\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Excel Vba Filter Range By Each Unique Value and Copy Data","datePublished":"2022-10-14T11:51:23+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/"},"wordCount":186,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["excel","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/","url":"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/","name":"[Solved] Excel Vba Filter Range By Each Unique Value and Copy Data - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-10-14T11:51:23+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-filter-range-by-each-unique-value-and-copy-data\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Excel Vba Filter Range By Each Unique Value and Copy 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=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\/16214","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=16214"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/16214\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=16214"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=16214"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=16214"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}