{"id":4643,"date":"2022-12-29T10:45:22","date_gmt":"2022-12-29T03:45:22","guid":{"rendered":"https:\/\/mahisaajy.web.id\/blog\/?p=4643"},"modified":"2022-12-29T10:46:22","modified_gmt":"2022-12-29T03:46:22","slug":"memecah-1-sheet-menjadi-beberapa-sheet-di-excel","status":"publish","type":"post","link":"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/","title":{"rendered":"Memecah 1 Sheet Menjadi Beberapa Sheet di Excel"},"content":{"rendered":"\n<p>Kode excel macro nya seperti ini:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Sub Splitdatabycol()\n'updateby Extendoffice\nDim lr As Long\nDim ws As Worksheet\nDim vcol, i As Integer\nDim icol As Long\nDim myarr As Variant\nDim title As String\nDim titlerow As Integer\nDim xTRg As Range\nDim xVRg As Range\nDim xWSTRg As Worksheet\nDim xWS As Worksheet\nOn Error Resume Next\nSet xTRg = Application.InputBox(\"Please select the header rows:\", \"Kutools for Excel\", \"\", Type:=8)\nIf TypeName(xTRg) = \"Nothing\" Then Exit Sub\nSet xVRg = Application.InputBox(\"Please select the column you want to split data based on:\", \"Kutools for Excel\", \"\", Type:=8)\nIf TypeName(xVRg) = \"Nothing\" Then Exit Sub\nvcol = xVRg.Column\nSet ws = xTRg.Worksheet\nlr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row\ntitle = xTRg.AddressLocal\ntitlerow = xTRg.Cells(1).Row\nicol = ws.Columns.Count\nws.Cells(1, icol) = \"Unique\"\nApplication.DisplayAlerts = False\nIf Not Evaluate(\"=ISREF('xTRgWs_Sheet!A1')\") Then\nSheets.Add(after:=Worksheets(Worksheets.Count)).Name = \"xTRgWs_Sheet\"\nElse\nSheets(\"xTRgWs_Sheet\").Delete\nSheets.Add(after:=Worksheets(Worksheets.Count)).Name = \"xTRgWs_Sheet\"\nEnd If\nSet xWSTRg = Sheets(\"xTRgWs_Sheet\")\nxTRg.Copy\nxWSTRg.Paste Destination:=xWSTRg.Range(\"A1\")\nws.Activate\nFor i = (titlerow + xTRg.Rows.Count) To lr\nOn Error Resume Next\nIf ws.Cells(i, vcol) &lt;> \"\" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then\nws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)\nEnd If\nNext\nmyarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))\nws.Columns(icol).Clear\nFor i = 2 To UBound(myarr)\nws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) &amp; \"\"\nIf Not Evaluate(\"=ISREF('\" &amp; myarr(i) &amp; \"'!A1)\") Then\nSet xWS = Sheets.Add(after:=Worksheets(Worksheets.Count))\nxWS.Name = myarr(i) &amp; \"\"\nElse\nxWS.Move after:=Worksheets(Worksheets.Count)\nEnd If\nxWSTRg.Range(title).Copy\nxWS.Paste Destination:=xWS.Range(\"A1\")\nws.Range(\"A\" &amp; (titlerow + xTRg.Rows.Count) &amp; \":A\" &amp; lr).EntireRow.Copy xWS.Range(\"A\" &amp; (titlerow + xTRg.Rows.Count))\nSheets(myarr(i) &amp; \"\").Columns.AutoFit\nNext\nxWSTRg.Delete\nws.AutoFilterMode = False\nws.Activate\nApplication.DisplayAlerts = True\nEnd Sub\n<\/code><\/pre>\n\n\n\n<p>Referensi: <a href=\"https:\/\/www.extendoffice.com\/documents\/excel\/1174-excel-split-data-into-multiple-worksheets-based-on-column.html\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.extendoffice.com\/documents\/excel\/1174-excel-split-data-into-multiple-worksheets-based-on-column.html<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Kode excel macro nya seperti ini: Referensi: https:\/\/www.extendoffice.com\/documents\/excel\/1174-excel-split-data-into-multiple-worksheets-based-on-column.html<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"_links_to":"","_links_to_target":""},"categories":[390],"tags":[],"class_list":["post-4643","post","type-post","status-publish","format-standard","hentry","category-excel"],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[]}},"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.0 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Memecah 1 Sheet Menjadi Beberapa Sheet di Excel - Mahisa Ajy Kusuma<\/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:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Memecah 1 Sheet Menjadi Beberapa Sheet di Excel - Mahisa Ajy Kusuma\" \/>\n<meta property=\"og:description\" content=\"Kode excel macro nya seperti ini: Referensi: https:\/\/www.extendoffice.com\/documents\/excel\/1174-excel-split-data-into-multiple-worksheets-based-on-column.html\" \/>\n<meta property=\"og:url\" content=\"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/\" \/>\n<meta property=\"og:site_name\" content=\"Mahisa Ajy Kusuma\" \/>\n<meta property=\"article:publisher\" content=\"mahisaajy\" \/>\n<meta property=\"article:author\" content=\"mahisaajy\" \/>\n<meta property=\"article:published_time\" content=\"2022-12-29T03:45:22+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-12-29T03:46:22+00:00\" \/>\n<meta name=\"author\" content=\"mahisaajy\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@mahisaajy\" \/>\n<meta name=\"twitter:site\" content=\"@mahisaajy\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"mahisaajy\" \/>\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:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/\"},\"author\":{\"name\":\"mahisaajy\",\"@id\":\"https:\/\/mahisaajy.web.id\/blog\/#\/schema\/person\/d9bfa055f34d78537afcc2d6079ff40c\"},\"headline\":\"Memecah 1 Sheet Menjadi Beberapa Sheet di Excel\",\"datePublished\":\"2022-12-29T03:45:22+00:00\",\"dateModified\":\"2022-12-29T03:46:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/\"},\"wordCount\":22,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/mahisaajy.web.id\/blog\/#\/schema\/person\/d9bfa055f34d78537afcc2d6079ff40c\"},\"articleSection\":[\"Excel\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/\",\"url\":\"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/\",\"name\":\"Memecah 1 Sheet Menjadi Beberapa Sheet di Excel - Mahisa Ajy Kusuma\",\"isPartOf\":{\"@id\":\"https:\/\/mahisaajy.web.id\/blog\/#website\"},\"datePublished\":\"2022-12-29T03:45:22+00:00\",\"dateModified\":\"2022-12-29T03:46:22+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/mahisaajy.web.id\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Memecah 1 Sheet Menjadi Beberapa Sheet di Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/mahisaajy.web.id\/blog\/#website\",\"url\":\"https:\/\/mahisaajy.web.id\/blog\/\",\"name\":\"Mahisa Ajy Kusuma\",\"description\":\"Kewajiban kita lebih banyak dari waktu yang tersedia.\",\"publisher\":{\"@id\":\"https:\/\/mahisaajy.web.id\/blog\/#\/schema\/person\/d9bfa055f34d78537afcc2d6079ff40c\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/mahisaajy.web.id\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/mahisaajy.web.id\/blog\/#\/schema\/person\/d9bfa055f34d78537afcc2d6079ff40c\",\"name\":\"mahisaajy\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/mahisaajy.web.id\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/84e68a607b6f3a00fdbccb0e208f3f66e31685e032a45bceac18338a091b5361?s=96&d=monsterid&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/84e68a607b6f3a00fdbccb0e208f3f66e31685e032a45bceac18338a091b5361?s=96&d=monsterid&r=g\",\"caption\":\"mahisaajy\"},\"logo\":{\"@id\":\"https:\/\/mahisaajy.web.id\/blog\/#\/schema\/person\/image\/\"},\"description\":\"Seorang pemuda yang mempunyai hobi menulis, membaca, dan bermusik. Tertarik dengan bidang ilmu komputer untuk memecahkan beberapa persoalan. Sejak tahun 2018 bekerja di BMKG di Direktorat Data dan Komputasi. Blog tentang Teknologi: Mahisadev.com\",\"sameAs\":[\"http:\/\/mahisaajy.web.id\/blog\/\",\"mahisaajy\",\"https:\/\/x.com\/mahisaajy\",\"mahisaful\"],\"url\":\"https:\/\/mahisaajy.web.id\/blog\/author\/mahisaajy\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Memecah 1 Sheet Menjadi Beberapa Sheet di Excel - Mahisa Ajy Kusuma","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:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/","og_locale":"en_US","og_type":"article","og_title":"Memecah 1 Sheet Menjadi Beberapa Sheet di Excel - Mahisa Ajy Kusuma","og_description":"Kode excel macro nya seperti ini: Referensi: https:\/\/www.extendoffice.com\/documents\/excel\/1174-excel-split-data-into-multiple-worksheets-based-on-column.html","og_url":"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/","og_site_name":"Mahisa Ajy Kusuma","article_publisher":"mahisaajy","article_author":"mahisaajy","article_published_time":"2022-12-29T03:45:22+00:00","article_modified_time":"2022-12-29T03:46:22+00:00","author":"mahisaajy","twitter_card":"summary_large_image","twitter_creator":"@mahisaajy","twitter_site":"@mahisaajy","twitter_misc":{"Written by":"mahisaajy","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/#article","isPartOf":{"@id":"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/"},"author":{"name":"mahisaajy","@id":"https:\/\/mahisaajy.web.id\/blog\/#\/schema\/person\/d9bfa055f34d78537afcc2d6079ff40c"},"headline":"Memecah 1 Sheet Menjadi Beberapa Sheet di Excel","datePublished":"2022-12-29T03:45:22+00:00","dateModified":"2022-12-29T03:46:22+00:00","mainEntityOfPage":{"@id":"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/"},"wordCount":22,"commentCount":0,"publisher":{"@id":"https:\/\/mahisaajy.web.id\/blog\/#\/schema\/person\/d9bfa055f34d78537afcc2d6079ff40c"},"articleSection":["Excel"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/","url":"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/","name":"Memecah 1 Sheet Menjadi Beberapa Sheet di Excel - Mahisa Ajy Kusuma","isPartOf":{"@id":"https:\/\/mahisaajy.web.id\/blog\/#website"},"datePublished":"2022-12-29T03:45:22+00:00","dateModified":"2022-12-29T03:46:22+00:00","breadcrumb":{"@id":"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/mahisaajy.web.id\/blog\/2022\/12\/memecah-1-sheet-menjadi-beberapa-sheet-di-excel\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/mahisaajy.web.id\/blog\/"},{"@type":"ListItem","position":2,"name":"Memecah 1 Sheet Menjadi Beberapa Sheet di Excel"}]},{"@type":"WebSite","@id":"https:\/\/mahisaajy.web.id\/blog\/#website","url":"https:\/\/mahisaajy.web.id\/blog\/","name":"Mahisa Ajy Kusuma","description":"Kewajiban kita lebih banyak dari waktu yang tersedia.","publisher":{"@id":"https:\/\/mahisaajy.web.id\/blog\/#\/schema\/person\/d9bfa055f34d78537afcc2d6079ff40c"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/mahisaajy.web.id\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/mahisaajy.web.id\/blog\/#\/schema\/person\/d9bfa055f34d78537afcc2d6079ff40c","name":"mahisaajy","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/mahisaajy.web.id\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/84e68a607b6f3a00fdbccb0e208f3f66e31685e032a45bceac18338a091b5361?s=96&d=monsterid&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/84e68a607b6f3a00fdbccb0e208f3f66e31685e032a45bceac18338a091b5361?s=96&d=monsterid&r=g","caption":"mahisaajy"},"logo":{"@id":"https:\/\/mahisaajy.web.id\/blog\/#\/schema\/person\/image\/"},"description":"Seorang pemuda yang mempunyai hobi menulis, membaca, dan bermusik. Tertarik dengan bidang ilmu komputer untuk memecahkan beberapa persoalan. Sejak tahun 2018 bekerja di BMKG di Direktorat Data dan Komputasi. Blog tentang Teknologi: Mahisadev.com","sameAs":["http:\/\/mahisaajy.web.id\/blog\/","mahisaajy","https:\/\/x.com\/mahisaajy","mahisaful"],"url":"https:\/\/mahisaajy.web.id\/blog\/author\/mahisaajy\/"}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p3PanY-1cT","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/mahisaajy.web.id\/blog\/wp-json\/wp\/v2\/posts\/4643","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mahisaajy.web.id\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mahisaajy.web.id\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mahisaajy.web.id\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mahisaajy.web.id\/blog\/wp-json\/wp\/v2\/comments?post=4643"}],"version-history":[{"count":2,"href":"https:\/\/mahisaajy.web.id\/blog\/wp-json\/wp\/v2\/posts\/4643\/revisions"}],"predecessor-version":[{"id":4645,"href":"https:\/\/mahisaajy.web.id\/blog\/wp-json\/wp\/v2\/posts\/4643\/revisions\/4645"}],"wp:attachment":[{"href":"https:\/\/mahisaajy.web.id\/blog\/wp-json\/wp\/v2\/media?parent=4643"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mahisaajy.web.id\/blog\/wp-json\/wp\/v2\/categories?post=4643"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mahisaajy.web.id\/blog\/wp-json\/wp\/v2\/tags?post=4643"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}