{"id":12794,"date":"2022-10-01T23:04:58","date_gmt":"2022-10-01T17:34:58","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/"},"modified":"2022-10-01T23:04:58","modified_gmt":"2022-10-01T17:34:58","slug":"solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/","title":{"rendered":"[Solved] Copy a partial row of data from one sheet to a new sheet within the same workbook based on cell content"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-16453713\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"16453713\" data-parentid=\"16453447\" data-score=\"1\" 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>Something like this should get you started. I have tried to comment it pretty thoroughly so as to explain what is happening in the macro:<\/p>\n<pre><code>Sub CopySomeCells()\nDim targetSheet As Worksheet 'destination for the copied cells'\nDim sourceSheet As Worksheet 'source of data worksheet'\nDim rng As Range 'range variable for all data'\nDim rngToCopy As Range 'range to copy'\nDim r As Long 'row counter'\nDim x As Long 'row finder'\nDim clientCode As String\nDim clientSheet As String\n\nSet sourceSheet = Worksheets(\"Sheet1\") '## The source data worksheet, modify as needed ##\n    With sourceSheet\n        '## the sheet may have data between rows 4 and 1000, modify as needed ##'\n        Set rng = .Range(\"A4\", Range(\"A1000\").End(xlUp))\n\n        '## iterate over the rows in the range we defined above ##'\n        For r = 1 To rng.Rows.Count\n\n\n            '## Set the range to copy ##'\n            Set rngToCopy = Range(rng.Cells(r, 1), rng.Cells(r, 12))\n\n            '## ignore rows that don't have a value in column F ##\n            If Not rng.Cells(r, 6).Value = vbNullString Then\n\n                '## Set the targetSheet dynamically, based on the code in column F ##'\n                '  e.g., \"ab1\" --&gt; Client 1, \"ab2\" --&gt; Client 2, etc. '\n                '## Set the client code ##\"\n                clientCode = rng.Cells(r, 6).Value\n\n                '## determine what sheet to use ##'\n                ' I do this by finding the client code in the lookup table, which\n                ' is in range \"O24:O37\", using the MATCH function.\n                ' Then, offset it -1 rows (the row above) which will tell us \"Client Code 1\", etc.\n\n                clientSheet = .Range(\"O23\").Offset( _\n                    Application.Match(clientCode, .Range(\"O24:O37\"), False), 0).Offset(-1, 0).Value\n                ' take that value \"Client Code 1\" and replace \"Code \" with nothing, so that\n                ' will then give us the sheet name, e.g., \"Client Code 1\" --&gt; \"Client 1\", etc. ##'\n                clientSheet = Replace(clientSheet, \"Code \", vbNullString)\n\n                Set targetSheet = Worksheets(clientSheet)\n\n                '## Find the next empty row in this worksheet ##'\n                x = Application.WorksheetFunction.CountA(targetSheet.Range(\"A:A\")) + 1\n\n                '## Copy the selected sub-range, ##'\n\n                rngToCopy.Copy \n\n                '## Paste values only to the target sheet ##'\n                targetSheet.Cells(x, 1).PasteSpecial Paste:=xlPasteValues, _\n                    Operation:=xlNone, SkipBlanks:=False, Transpose:=False\n\n            End If\n\n        Next '## proceed to process the next row in this range ##'\n\n    End With\n\nEnd Sub\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">33<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved Copy a partial row of data from one sheet to a new sheet within the same workbook based on cell content <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Something like this should get you started. I have tried to comment it pretty thoroughly so as to explain what is happening in the macro: Sub CopySomeCells() Dim targetSheet As Worksheet &#8216;destination for the copied cells&#8217; Dim sourceSheet As Worksheet &#8216;source of data worksheet&#8217; Dim rng As Range &#8216;range variable for all data&#8217; Dim &#8230; <a title=\"[Solved] Copy a partial row of data from one sheet to a new sheet within the same workbook based on cell content\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/\" aria-label=\"More on [Solved] Copy a partial row of data from one sheet to a new sheet within the same workbook based on cell content\">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,1612,401],"class_list":["post-12794","post","type-post","status-publish","format-standard","hentry","category-solved","tag-excel","tag-excel-2010","tag-vba"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] Copy a partial row of data from one sheet to a new sheet within the same workbook based on cell content - 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-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Copy a partial row of data from one sheet to a new sheet within the same workbook based on cell content - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Something like this should get you started. I have tried to comment it pretty thoroughly so as to explain what is happening in the macro: Sub CopySomeCells() Dim targetSheet As Worksheet &#039;destination for the copied cells&#039; Dim sourceSheet As Worksheet &#039;source of data worksheet&#039; Dim rng As Range &#039;range variable for all data&#039; Dim ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-01T17:34:58+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-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Copy a partial row of data from one sheet to a new sheet within the same workbook based on cell content\",\"datePublished\":\"2022-10-01T17:34:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/\"},\"wordCount\":71,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"excel\",\"excel-2010\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/\",\"name\":\"[Solved] Copy a partial row of data from one sheet to a new sheet within the same workbook based on cell content - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-10-01T17:34:58+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Copy a partial row of data from one sheet to a new sheet within the same workbook based on cell content\"}]},{\"@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] Copy a partial row of data from one sheet to a new sheet within the same workbook based on cell content - 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-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Copy a partial row of data from one sheet to a new sheet within the same workbook based on cell content - JassWeb","og_description":"[ad_1] Something like this should get you started. I have tried to comment it pretty thoroughly so as to explain what is happening in the macro: Sub CopySomeCells() Dim targetSheet As Worksheet 'destination for the copied cells' Dim sourceSheet As Worksheet 'source of data worksheet' Dim rng As Range 'range variable for all data' Dim ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/","og_site_name":"JassWeb","article_published_time":"2022-10-01T17:34:58+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-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Copy a partial row of data from one sheet to a new sheet within the same workbook based on cell content","datePublished":"2022-10-01T17:34:58+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/"},"wordCount":71,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["excel","excel-2010","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/","url":"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/","name":"[Solved] Copy a partial row of data from one sheet to a new sheet within the same workbook based on cell content - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-10-01T17:34:58+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-copy-a-partial-row-of-data-from-one-sheet-to-a-new-sheet-within-the-same-workbook-based-on-cell-content\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Copy a partial row of data from one sheet to a new sheet within the same workbook based on cell content"}]},{"@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\/12794","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=12794"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/12794\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=12794"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=12794"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=12794"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}