Yth. Pak HK formula counta di sell K1 sudah dilakukan
On Friday, March 24, 2017 10:07 AM, "hendrik karnadi hendrikkarn...@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> wrote: Yang ini sudah dilakukan >> Pada Sheet Gabung sel K1 tulis formula ini =COUNTA(A:A) ? Salam,HK From: "Eira I Arie cheivory_bri...@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> To: belajar-excel@yahoogroups.com Sent: Friday, 24 March 2017, 9:31 Subject: Re: [belajar-excel] Macro Gabung Sheet Yth. Pak HK Sudah saya coba tetapi ada debug di barisan berikut : ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$I$" & Range("K1")), , xlYes).Name = _ "Tableku" note : penamaan excel table sudah saya sesuaikan "Tableku" ... kalau barisan yang debug itu saya hapus maka, format excel tabel akan hilang saat setelah proses gabung Terima kasih On Thursday, March 23, 2017 2:11 PM, "hendrik karnadi hendrikkarn...@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> wrote: Hai Ari, Pada Sheet Gabung sel K1 tulis formula ini =COUNTA(A:A) Di VBE Sheet sebelum Application.CutCopyMode = False, copas 3 perintah ini : Columns("A:I").FormatConditions.DeleteColumns("A:I").Interior.Color = xlNoneActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$I$" & Range("K1")), , xlYes).Name = _ "Tableku" Selamat mencoba. Salam,HK From: "Eira I Arie cheivory_bri...@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> To: belajar-excel@yahoogroups.com Sent: Thursday, 23 March 2017, 12:42 Subject: Re: [belajar-excel] Macro Gabung Sheet [1 Attachment] 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 #yiv7789957230 #yiv7789957230 -- #yiv7789957230ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv7789957230 #yiv7789957230ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv7789957230 #yiv7789957230ygrp-mkp #yiv7789957230hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv7789957230 #yiv7789957230ygrp-mkp #yiv7789957230ads {margin-bottom:10px;}#yiv7789957230 #yiv7789957230ygrp-mkp .yiv7789957230ad {padding:0 0;}#yiv7789957230 #yiv7789957230ygrp-mkp .yiv7789957230ad p {margin:0;}#yiv7789957230 #yiv7789957230ygrp-mkp .yiv7789957230ad a {color:#0000ff;text-decoration:none;}#yiv7789957230 #yiv7789957230ygrp-sponsor #yiv7789957230ygrp-lc {font-family:Arial;}#yiv7789957230 #yiv7789957230ygrp-sponsor #yiv7789957230ygrp-lc #yiv7789957230hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv7789957230 #yiv7789957230ygrp-sponsor #yiv7789957230ygrp-lc .yiv7789957230ad {margin-bottom:10px;padding:0 0;}#yiv7789957230 #yiv7789957230actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv7789957230 #yiv7789957230activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv7789957230 #yiv7789957230activity span {font-weight:700;}#yiv7789957230 #yiv7789957230activity span:first-child {text-transform:uppercase;}#yiv7789957230 #yiv7789957230activity span a {color:#5085b6;text-decoration:none;}#yiv7789957230 #yiv7789957230activity span span {color:#ff7900;}#yiv7789957230 #yiv7789957230activity span .yiv7789957230underline {text-decoration:underline;}#yiv7789957230 .yiv7789957230attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv7789957230 .yiv7789957230attach div a {text-decoration:none;}#yiv7789957230 .yiv7789957230attach img {border:none;padding-right:5px;}#yiv7789957230 .yiv7789957230attach label {display:block;margin-bottom:5px;}#yiv7789957230 .yiv7789957230attach label a {text-decoration:none;}#yiv7789957230 blockquote {margin:0 0 0 4px;}#yiv7789957230 .yiv7789957230bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv7789957230 .yiv7789957230bold a {text-decoration:none;}#yiv7789957230 dd.yiv7789957230last p a {font-family:Verdana;font-weight:700;}#yiv7789957230 dd.yiv7789957230last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv7789957230 dd.yiv7789957230last p span.yiv7789957230yshortcuts {margin-right:0;}#yiv7789957230 div.yiv7789957230attach-table div div a {text-decoration:none;}#yiv7789957230 div.yiv7789957230attach-table {width:400px;}#yiv7789957230 div.yiv7789957230file-title a, #yiv7789957230 div.yiv7789957230file-title a:active, #yiv7789957230 div.yiv7789957230file-title a:hover, #yiv7789957230 div.yiv7789957230file-title a:visited {text-decoration:none;}#yiv7789957230 div.yiv7789957230photo-title a, #yiv7789957230 div.yiv7789957230photo-title a:active, #yiv7789957230 div.yiv7789957230photo-title a:hover, #yiv7789957230 div.yiv7789957230photo-title a:visited {text-decoration:none;}#yiv7789957230 div#yiv7789957230ygrp-mlmsg #yiv7789957230ygrp-msg p a span.yiv7789957230yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv7789957230 .yiv7789957230green {color:#628c2a;}#yiv7789957230 .yiv7789957230MsoNormal {margin:0 0 0 0;}#yiv7789957230 o {font-size:0;}#yiv7789957230 #yiv7789957230photos div {float:left;width:72px;}#yiv7789957230 #yiv7789957230photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv7789957230 #yiv7789957230photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv7789957230 #yiv7789957230reco-category {font-size:77%;}#yiv7789957230 #yiv7789957230reco-desc {font-size:77%;}#yiv7789957230 .yiv7789957230replbq {margin:4px;}#yiv7789957230 #yiv7789957230ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv7789957230 #yiv7789957230ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv7789957230 #yiv7789957230ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv7789957230 #yiv7789957230ygrp-mlmsg select, #yiv7789957230 input, #yiv7789957230 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv7789957230 #yiv7789957230ygrp-mlmsg pre, #yiv7789957230 code {font:115% monospace;}#yiv7789957230 #yiv7789957230ygrp-mlmsg * {line-height:1.22em;}#yiv7789957230 #yiv7789957230ygrp-mlmsg #yiv7789957230logo {padding-bottom:10px;}#yiv7789957230 #yiv7789957230ygrp-msg p a {font-family:Verdana;}#yiv7789957230 #yiv7789957230ygrp-msg p#yiv7789957230attach-count span {color:#1E66AE;font-weight:700;}#yiv7789957230 #yiv7789957230ygrp-reco #yiv7789957230reco-head {color:#ff7900;font-weight:700;}#yiv7789957230 #yiv7789957230ygrp-reco {margin-bottom:20px;padding:0px;}#yiv7789957230 #yiv7789957230ygrp-sponsor #yiv7789957230ov li a {font-size:130%;text-decoration:none;}#yiv7789957230 #yiv7789957230ygrp-sponsor #yiv7789957230ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv7789957230 #yiv7789957230ygrp-sponsor #yiv7789957230ov ul {margin:0;padding:0 0 0 8px;}#yiv7789957230 #yiv7789957230ygrp-text {font-family:Georgia;}#yiv7789957230 #yiv7789957230ygrp-text p {margin:0 0 1em 0;}#yiv7789957230 #yiv7789957230ygrp-text tt {font-size:120%;}#yiv7789957230 #yiv7789957230ygrp-vital ul li:last-child {border-right:none !important;}#yiv7789957230