Yang ini sudah dilakukan >> Pada Sheet Gabung sel K1 tulis formula ini
=COUNTA(A:A) ?
Salam,HK
From: "Eira I Arie [email protected] [belajar-excel]"
<[email protected]>
To: [email protected]
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
[email protected] [belajar-excel]" <[email protected]> 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 [email protected] [belajar-excel]"
<[email protected]>
To: [email protected]
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 [email protected]
[belajar-excel]" <[email protected]> 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 [email protected]
[belajar-excel] <[email protected]> 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
#yiv8310650647 #yiv8310650647 -- #yiv8310650647ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv8310650647
#yiv8310650647ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv8310650647
#yiv8310650647ygrp-mkp #yiv8310650647hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv8310650647 #yiv8310650647ygrp-mkp #yiv8310650647ads
{margin-bottom:10px;}#yiv8310650647 #yiv8310650647ygrp-mkp .yiv8310650647ad
{padding:0 0;}#yiv8310650647 #yiv8310650647ygrp-mkp .yiv8310650647ad p
{margin:0;}#yiv8310650647 #yiv8310650647ygrp-mkp .yiv8310650647ad a
{color:#0000ff;text-decoration:none;}#yiv8310650647 #yiv8310650647ygrp-sponsor
#yiv8310650647ygrp-lc {font-family:Arial;}#yiv8310650647
#yiv8310650647ygrp-sponsor #yiv8310650647ygrp-lc #yiv8310650647hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv8310650647
#yiv8310650647ygrp-sponsor #yiv8310650647ygrp-lc .yiv8310650647ad
{margin-bottom:10px;padding:0 0;}#yiv8310650647 #yiv8310650647actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv8310650647
#yiv8310650647activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv8310650647
#yiv8310650647activity span {font-weight:700;}#yiv8310650647
#yiv8310650647activity span:first-child
{text-transform:uppercase;}#yiv8310650647 #yiv8310650647activity span a
{color:#5085b6;text-decoration:none;}#yiv8310650647 #yiv8310650647activity span
span {color:#ff7900;}#yiv8310650647 #yiv8310650647activity span
.yiv8310650647underline {text-decoration:underline;}#yiv8310650647
.yiv8310650647attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv8310650647 .yiv8310650647attach div a
{text-decoration:none;}#yiv8310650647 .yiv8310650647attach img
{border:none;padding-right:5px;}#yiv8310650647 .yiv8310650647attach label
{display:block;margin-bottom:5px;}#yiv8310650647 .yiv8310650647attach label a
{text-decoration:none;}#yiv8310650647 blockquote {margin:0 0 0
4px;}#yiv8310650647 .yiv8310650647bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv8310650647
.yiv8310650647bold a {text-decoration:none;}#yiv8310650647 dd.yiv8310650647last
p a {font-family:Verdana;font-weight:700;}#yiv8310650647 dd.yiv8310650647last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv8310650647
dd.yiv8310650647last p span.yiv8310650647yshortcuts
{margin-right:0;}#yiv8310650647 div.yiv8310650647attach-table div div a
{text-decoration:none;}#yiv8310650647 div.yiv8310650647attach-table
{width:400px;}#yiv8310650647 div.yiv8310650647file-title a, #yiv8310650647
div.yiv8310650647file-title a:active, #yiv8310650647
div.yiv8310650647file-title a:hover, #yiv8310650647 div.yiv8310650647file-title
a:visited {text-decoration:none;}#yiv8310650647 div.yiv8310650647photo-title a,
#yiv8310650647 div.yiv8310650647photo-title a:active, #yiv8310650647
div.yiv8310650647photo-title a:hover, #yiv8310650647
div.yiv8310650647photo-title a:visited {text-decoration:none;}#yiv8310650647
div#yiv8310650647ygrp-mlmsg #yiv8310650647ygrp-msg p a
span.yiv8310650647yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv8310650647
.yiv8310650647green {color:#628c2a;}#yiv8310650647 .yiv8310650647MsoNormal
{margin:0 0 0 0;}#yiv8310650647 o {font-size:0;}#yiv8310650647
#yiv8310650647photos div {float:left;width:72px;}#yiv8310650647
#yiv8310650647photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv8310650647
#yiv8310650647photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv8310650647
#yiv8310650647reco-category {font-size:77%;}#yiv8310650647
#yiv8310650647reco-desc {font-size:77%;}#yiv8310650647 .yiv8310650647replbq
{margin:4px;}#yiv8310650647 #yiv8310650647ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv8310650647 #yiv8310650647ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv8310650647
#yiv8310650647ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv8310650647
#yiv8310650647ygrp-mlmsg select, #yiv8310650647 input, #yiv8310650647 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv8310650647
#yiv8310650647ygrp-mlmsg pre, #yiv8310650647 code {font:115%
monospace;}#yiv8310650647 #yiv8310650647ygrp-mlmsg *
{line-height:1.22em;}#yiv8310650647 #yiv8310650647ygrp-mlmsg #yiv8310650647logo
{padding-bottom:10px;}#yiv8310650647 #yiv8310650647ygrp-msg p a
{font-family:Verdana;}#yiv8310650647 #yiv8310650647ygrp-msg
p#yiv8310650647attach-count span {color:#1E66AE;font-weight:700;}#yiv8310650647
#yiv8310650647ygrp-reco #yiv8310650647reco-head
{color:#ff7900;font-weight:700;}#yiv8310650647 #yiv8310650647ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv8310650647 #yiv8310650647ygrp-sponsor
#yiv8310650647ov li a {font-size:130%;text-decoration:none;}#yiv8310650647
#yiv8310650647ygrp-sponsor #yiv8310650647ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv8310650647
#yiv8310650647ygrp-sponsor #yiv8310650647ov ul {margin:0;padding:0 0 0
8px;}#yiv8310650647 #yiv8310650647ygrp-text
{font-family:Georgia;}#yiv8310650647 #yiv8310650647ygrp-text p {margin:0 0 1em
0;}#yiv8310650647 #yiv8310650647ygrp-text tt {font-size:120%;}#yiv8310650647
#yiv8310650647ygrp-vital ul li:last-child {border-right:none
!important;}#yiv8310650647
