{"id":5166,"date":"2022-08-27T01:19:05","date_gmt":"2022-08-26T19:49:05","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-excel-vba-return-separated-data\/"},"modified":"2022-08-27T01:19:05","modified_gmt":"2022-08-26T19:49:05","slug":"solved-excel-vba-return-separated-data","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-excel-vba-return-separated-data\/","title":{"rendered":"[Solved] Excel VBA &#8211; Return separated data"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-48092126\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"48092126\" data-parentid=\"48091323\" 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>If you want your result to be in Sheet2, then this code will do what you expect, it will check the number of Columns on Sheet1 and copy all of them into Sheet2:<\/p>\n<pre><code>Sub foo()\nDim LastRow As Long\nDim LastCol As Long\nDim ws As Worksheet: Set ws = Sheets(\"Sheet1\") 'change this to the name of your worksheet\nDim ws2 As Worksheet: Set ws2 = Sheets(\"Sheet2\")\nLastRow = ws.Cells(ws.Rows.Count, \"A\").End(xlUp).Row 'get the last row\nLastCol = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column 'get the number of columns from row 2\nFor i = 2 To LastRow\nstrTest = ws.Cells(i, 1)\nMyarray = Split(strTest, Chr(10)) 'split the values on the first column into an array\n    For x = LBound(Myarray) To UBound(Myarray) ' loop through array\n        LastRow2 = ws2.Cells(ws2.Rows.Count, \"A\").End(xlUp).Row 'get the last row of Sheet2\n        ws2.Cells(LastRow2 + 1, 1).Value = Myarray(x) 'paste the contents into Sheet2\n        For y = 2 To LastCol 'loop for the number of columns on Sheet1\n            ws2.Cells(LastRow2 + 1, y).Value = ws.Cells(i, y) 'paste all relevant columns into Sheet2\n        Next y\n    Next x\nNext i\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 Excel VBA &#8211; Return separated data <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] If you want your result to be in Sheet2, then this code will do what you expect, it will check the number of Columns on Sheet1 and copy all of them into Sheet2: Sub foo() Dim LastRow As Long Dim LastCol As Long Dim ws As Worksheet: Set ws = Sheets(&#8220;Sheet1&#8221;) &#8216;change this to &#8230; <a title=\"[Solved] Excel VBA &#8211; Return separated data\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-excel-vba-return-separated-data\/\" aria-label=\"More on [Solved] Excel VBA &#8211; Return separated 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-5166","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 - Return separated 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-return-separated-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 - Return separated data - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] If you want your result to be in Sheet2, then this code will do what you expect, it will check the number of Columns on Sheet1 and copy all of them into Sheet2: Sub foo() Dim LastRow As Long Dim LastCol As Long Dim ws As Worksheet: Set ws = Sheets(&quot;Sheet1&quot;) &#039;change this to ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-excel-vba-return-separated-data\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-08-26T19:49:05+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-return-separated-data\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-return-separated-data\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Excel VBA &#8211; Return separated data\",\"datePublished\":\"2022-08-26T19:49:05+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-return-separated-data\/\"},\"wordCount\":49,\"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-return-separated-data\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-return-separated-data\/\",\"name\":\"[Solved] Excel VBA - Return separated data - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-08-26T19:49:05+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-return-separated-data\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-excel-vba-return-separated-data\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-excel-vba-return-separated-data\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Excel VBA &#8211; Return separated 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=1776403586\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"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 - Return separated 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-return-separated-data\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Excel VBA - Return separated data - JassWeb","og_description":"[ad_1] If you want your result to be in Sheet2, then this code will do what you expect, it will check the number of Columns on Sheet1 and copy all of them into Sheet2: Sub foo() Dim LastRow As Long Dim LastCol As Long Dim ws As Worksheet: Set ws = Sheets(\"Sheet1\") 'change this to ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-excel-vba-return-separated-data\/","og_site_name":"JassWeb","article_published_time":"2022-08-26T19:49:05+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-return-separated-data\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-return-separated-data\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Excel VBA &#8211; Return separated data","datePublished":"2022-08-26T19:49:05+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-return-separated-data\/"},"wordCount":49,"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-return-separated-data\/","url":"https:\/\/jassweb.com\/solved\/solved-excel-vba-return-separated-data\/","name":"[Solved] Excel VBA - Return separated data - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-08-26T19:49:05+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-return-separated-data\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-excel-vba-return-separated-data\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-return-separated-data\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Excel VBA &#8211; Return separated 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=1776403586","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","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\/5166","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=5166"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/5166\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=5166"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=5166"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=5166"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}