Yth. Mr. Kid Langkah arahan Mr. Kid sudah saya lakukan : - Langkah 1 = sudah dilakukan - Langkah 2 = baris record semua sudah di hapus tetapi menyisakan baris record kosong excel table (selain header) - Langkah 3 = sudah dilakukan - Langkah 4 = sudah dilakukan dengan macro copasvalues contoh 2 - Langkah 5 = sudah dilakukan
Problemnya : - Saat proses gabung, baris record hasil gabung mulai muncul di A3 (setelah sisa satu baris record kosong excel table yang dihapus pada langkah 2) - Jadi, hasil gabung tidak menempati excel table .. - Saat proses hapus, terjadi debug .. tetapi jika record hasil gabung saya tempatkan manual di excel table (mulai A2 atau setelah header excel table) maka, proses hapus berjalan sukses .. Sekian Mr. Kid, mohon solusinya.Terima kasih On Friday, March 24, 2017 10:18 AM, "'Mr. Kid' mr.nm...@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> wrote: Hai Ari, Coba begini : 1. blok seluruh cells di sheet gabung -> atur agar fill color adalah No Fill -> hapus seluruh conditional formatting yang ada di sheet gabung 2. blok baris records dari Excel table di sheet gabung (tanpa header di seluruh kolom Excel table) -> lakukan delete table rows 3. save workbook 4. Cara copy paste values : sheets("nama sheet yang mau dicopy").range("a1").currentregion.offset(1).copy with sheets("nama sheet gabung") .cells( .rows.count , 1 ).end(xlup).offset(1).pastespecial xlpastevalues end with 5. Cara hapus seluruh records dalam Excel table (misal Excel table di sheet gabung yang header-nya mulai dari A1) with sheets("nama sheet gabung").range("a1").currentregion .offset(1).resize( .rows.count -1 ).delete xlshiftup end with *** Setelah 5 langkah di atas dikerjakan, barulah memikirkan perbaikan proses copy yang di paste values beserta proses delete excel table rows >> Jika hanya sheet tertentu saja yang akan di-copy lalu di-paste values ke >> sheet gabung, maka perlu menentukan pola penamaan sheet-nya. Contoh 1 : sheet yang digabung hanya yang berawalah kata 'sheet' dim sht as worksheet with sheets("nama sheet gabung") for each sht in thisworkbook.worksheets if lcase(left(sht.name,5))="sheet" then sht.range("a1").currentregion.offset(1).copy .cells( .rows.count , 1 ).end(xlup).offset(1).pastespecial xlpastevalues endif next sht end with Contoh 2 : sheet yang digabung adalah selain yang bernama 'Gabung','a1','a2','a3','kid' dim sht as worksheet, sShtName as string sshtname="\gabung\a1\a2\a3\kid\" with sheets("nama sheet gabung") for each sht in thisworkbook.worksheets if instr(sshtname,"\" & lcase(sht.name) & "\")=0 then sht.range("a1").currentregion.offset(1).copy .cells( .rows.count , 1 ).end(xlup).offset(1).pastespecial xlpastevalues endif next sht end with Regards, Kid On Thu, Mar 23, 2017 at 10:32 AM, Eira I Arie cheivory_bri...@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> wrote: Yth. Pak HK dan Pak Lukman file terlampir dari milis ini juga (sudah ditambahkan baris code "copypastevalue" arahan Pak HK) .. Problemnya begini , Jika misalnya : - pada sheet 1 , di kolom A diberi Conditional Formatting - pada sheet 2 , di kolom C diberi warna sell kuning di beberapa data - pada sheet gabung , dibuat excel table untuk wadah setelah diproses gabung , dalam sheet gabung terdapat Conditional Formatting (sesuai di sheet 1) dan warna sell (sesuai sheet 2). keinginannya hanya "copy value" . ternyata hasilnya masih terdapat CF dan warna sell seperti contoh terlampir .. terkadang , setelah proses tombol hapus, lalu proses gabung lagi maka, pada sheet gabung, format excel table menjadi hilang (jadinya range) .. mohon solusi atas permasalahan di atas .. Terima kasih Ari On Wednesday, March 22, 2017 7:46 PM, "lkm jktind lkmjkt...@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com > wrote: Pak cheivory_bridge. tolog lampirkan contoh file nya . yang lengkap ya. Salam Lukman. NB : Ada waktu luang dikit . On Wed, Mar 22, 2017 at 3:26 PM, hendrik karnadi hendrikkarn...@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com > wrote: Coba tambahkan 2 perintah berikut ini: Set rngGabung = Sheets("Gabung").Range("A2"). CurrentRegion rngGabung.Value = rngGabung.value seblm Application.CutCopyMode = False Salam,HK Sent from Yahoo Mail on Android Yth. Pakar Excel Mohon bantuannya , Saya dapat barisan code macro berikut (dari milis ini juga) : Sub Gabung() Dim rngGabung As Range Dim sh As Worksheet Dim rngsh As Range Dim brs As Long Set rngGabung = Sheets("Gabung").Range("A2"). CurrentRegion Set rngGabung = rngGabung.Offset(1, 0).Resize(rngGabung.Rows.Count - 1) Application.ScreenUpdating = False rngGabung.ClearContents For Each sh In Worksheets If sh.Name <> "Gabung" Then If sh.Name <> "nim_nama" Then If sh.Name <> "a2" Then If sh.Name <> "pivot_table" Then Set rngsh = sh.Range("A2").CurrentRegion Set rngsh = rngsh.Offset(1, 0).Resize(rngsh.Rows.Count - 1) brs = WorksheetFunction.CountA( Sheets("Gabung").Columns("A:A" )) rngsh.Copy Sheets("Gabung").Range("A" & brs + 1) End If End If End If End If Next sh Application.CutCopyMode = False Application.ScreenUpdating = TrueEnd Sub Barisan code macro tersebut jika diterapkan hasilnya "copy paste" .. keinginan saya bagaimana biar hasilnya "copy VALUE" saja, mohon bantuannya merevisi barisan code macro tersebut .. Terima kasih ari #yiv8975666513 #yiv8975666513 -- #yiv8975666513ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv8975666513 #yiv8975666513ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv8975666513 #yiv8975666513ygrp-mkp #yiv8975666513hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv8975666513 #yiv8975666513ygrp-mkp #yiv8975666513ads {margin-bottom:10px;}#yiv8975666513 #yiv8975666513ygrp-mkp .yiv8975666513ad {padding:0 0;}#yiv8975666513 #yiv8975666513ygrp-mkp .yiv8975666513ad p {margin:0;}#yiv8975666513 #yiv8975666513ygrp-mkp .yiv8975666513ad a {color:#0000ff;text-decoration:none;}#yiv8975666513 #yiv8975666513ygrp-sponsor #yiv8975666513ygrp-lc {font-family:Arial;}#yiv8975666513 #yiv8975666513ygrp-sponsor #yiv8975666513ygrp-lc #yiv8975666513hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv8975666513 #yiv8975666513ygrp-sponsor #yiv8975666513ygrp-lc .yiv8975666513ad {margin-bottom:10px;padding:0 0;}#yiv8975666513 #yiv8975666513actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv8975666513 #yiv8975666513activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv8975666513 #yiv8975666513activity span {font-weight:700;}#yiv8975666513 #yiv8975666513activity span:first-child {text-transform:uppercase;}#yiv8975666513 #yiv8975666513activity span a {color:#5085b6;text-decoration:none;}#yiv8975666513 #yiv8975666513activity span span {color:#ff7900;}#yiv8975666513 #yiv8975666513activity span .yiv8975666513underline {text-decoration:underline;}#yiv8975666513 .yiv8975666513attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv8975666513 .yiv8975666513attach div a {text-decoration:none;}#yiv8975666513 .yiv8975666513attach img {border:none;padding-right:5px;}#yiv8975666513 .yiv8975666513attach label {display:block;margin-bottom:5px;}#yiv8975666513 .yiv8975666513attach label a {text-decoration:none;}#yiv8975666513 blockquote {margin:0 0 0 4px;}#yiv8975666513 .yiv8975666513bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv8975666513 .yiv8975666513bold a {text-decoration:none;}#yiv8975666513 dd.yiv8975666513last p a {font-family:Verdana;font-weight:700;}#yiv8975666513 dd.yiv8975666513last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv8975666513 dd.yiv8975666513last p span.yiv8975666513yshortcuts {margin-right:0;}#yiv8975666513 div.yiv8975666513attach-table div div a {text-decoration:none;}#yiv8975666513 div.yiv8975666513attach-table {width:400px;}#yiv8975666513 div.yiv8975666513file-title a, #yiv8975666513 div.yiv8975666513file-title a:active, #yiv8975666513 div.yiv8975666513file-title a:hover, #yiv8975666513 div.yiv8975666513file-title a:visited {text-decoration:none;}#yiv8975666513 div.yiv8975666513photo-title a, #yiv8975666513 div.yiv8975666513photo-title a:active, #yiv8975666513 div.yiv8975666513photo-title a:hover, #yiv8975666513 div.yiv8975666513photo-title a:visited {text-decoration:none;}#yiv8975666513 div#yiv8975666513ygrp-mlmsg #yiv8975666513ygrp-msg p a span.yiv8975666513yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv8975666513 .yiv8975666513green {color:#628c2a;}#yiv8975666513 .yiv8975666513MsoNormal {margin:0 0 0 0;}#yiv8975666513 o {font-size:0;}#yiv8975666513 #yiv8975666513photos div {float:left;width:72px;}#yiv8975666513 #yiv8975666513photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv8975666513 #yiv8975666513photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv8975666513 #yiv8975666513reco-category {font-size:77%;}#yiv8975666513 #yiv8975666513reco-desc {font-size:77%;}#yiv8975666513 .yiv8975666513replbq {margin:4px;}#yiv8975666513 #yiv8975666513ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv8975666513 #yiv8975666513ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv8975666513 #yiv8975666513ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv8975666513 #yiv8975666513ygrp-mlmsg select, #yiv8975666513 input, #yiv8975666513 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv8975666513 #yiv8975666513ygrp-mlmsg pre, #yiv8975666513 code {font:115% monospace;}#yiv8975666513 #yiv8975666513ygrp-mlmsg * {line-height:1.22em;}#yiv8975666513 #yiv8975666513ygrp-mlmsg #yiv8975666513logo {padding-bottom:10px;}#yiv8975666513 #yiv8975666513ygrp-msg p a {font-family:Verdana;}#yiv8975666513 #yiv8975666513ygrp-msg p#yiv8975666513attach-count span {color:#1E66AE;font-weight:700;}#yiv8975666513 #yiv8975666513ygrp-reco #yiv8975666513reco-head {color:#ff7900;font-weight:700;}#yiv8975666513 #yiv8975666513ygrp-reco {margin-bottom:20px;padding:0px;}#yiv8975666513 #yiv8975666513ygrp-sponsor #yiv8975666513ov li a {font-size:130%;text-decoration:none;}#yiv8975666513 #yiv8975666513ygrp-sponsor #yiv8975666513ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv8975666513 #yiv8975666513ygrp-sponsor #yiv8975666513ov ul {margin:0;padding:0 0 0 8px;}#yiv8975666513 #yiv8975666513ygrp-text {font-family:Georgia;}#yiv8975666513 #yiv8975666513ygrp-text p {margin:0 0 1em 0;}#yiv8975666513 #yiv8975666513ygrp-text tt {font-size:120%;}#yiv8975666513 #yiv8975666513ygrp-vital ul li:last-child {border-right:none !important;}#yiv8975666513