Yth. Pak HK .. sudah saya coba file dari bapak .. berhasil .. terima kasihYth.
Mr. Kid .. terima kasih , akan dicoba
On Friday, March 31, 2017 4:03 PM, "hendrik karnadi
[email protected] [belajar-excel]" <[email protected]> wrote:
Hai Arie,
Kalo ga salah saya juga ada kirim email (file revisi) tgl 24 Maret.Sdh dicoba ?
Salam,HK
From: "Eira I Arie [email protected] [belajar-excel]"
<[email protected]>
To: [email protected]
Sent: Friday, 31 March 2017, 15:10
Subject: Re: [belajar-excel] Macro Gabung Sheet
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' [email protected]
[belajar-excel]" <[email protected]> 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 [email protected]
[belajar-excel] <[email protected]> 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 [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
#yiv5235546321 #yiv5235546321 -- #yiv5235546321ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5235546321
#yiv5235546321ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5235546321
#yiv5235546321ygrp-mkp #yiv5235546321hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv5235546321 #yiv5235546321ygrp-mkp #yiv5235546321ads
{margin-bottom:10px;}#yiv5235546321 #yiv5235546321ygrp-mkp .yiv5235546321ad
{padding:0 0;}#yiv5235546321 #yiv5235546321ygrp-mkp .yiv5235546321ad p
{margin:0;}#yiv5235546321 #yiv5235546321ygrp-mkp .yiv5235546321ad a
{color:#0000ff;text-decoration:none;}#yiv5235546321 #yiv5235546321ygrp-sponsor
#yiv5235546321ygrp-lc {font-family:Arial;}#yiv5235546321
#yiv5235546321ygrp-sponsor #yiv5235546321ygrp-lc #yiv5235546321hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv5235546321
#yiv5235546321ygrp-sponsor #yiv5235546321ygrp-lc .yiv5235546321ad
{margin-bottom:10px;padding:0 0;}#yiv5235546321 #yiv5235546321actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv5235546321
#yiv5235546321activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv5235546321
#yiv5235546321activity span {font-weight:700;}#yiv5235546321
#yiv5235546321activity span:first-child
{text-transform:uppercase;}#yiv5235546321 #yiv5235546321activity span a
{color:#5085b6;text-decoration:none;}#yiv5235546321 #yiv5235546321activity span
span {color:#ff7900;}#yiv5235546321 #yiv5235546321activity span
.yiv5235546321underline {text-decoration:underline;}#yiv5235546321
.yiv5235546321attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv5235546321 .yiv5235546321attach div a
{text-decoration:none;}#yiv5235546321 .yiv5235546321attach img
{border:none;padding-right:5px;}#yiv5235546321 .yiv5235546321attach label
{display:block;margin-bottom:5px;}#yiv5235546321 .yiv5235546321attach label a
{text-decoration:none;}#yiv5235546321 blockquote {margin:0 0 0
4px;}#yiv5235546321 .yiv5235546321bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv5235546321
.yiv5235546321bold a {text-decoration:none;}#yiv5235546321 dd.yiv5235546321last
p a {font-family:Verdana;font-weight:700;}#yiv5235546321 dd.yiv5235546321last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv5235546321
dd.yiv5235546321last p span.yiv5235546321yshortcuts
{margin-right:0;}#yiv5235546321 div.yiv5235546321attach-table div div a
{text-decoration:none;}#yiv5235546321 div.yiv5235546321attach-table
{width:400px;}#yiv5235546321 div.yiv5235546321file-title a, #yiv5235546321
div.yiv5235546321file-title a:active, #yiv5235546321
div.yiv5235546321file-title a:hover, #yiv5235546321 div.yiv5235546321file-title
a:visited {text-decoration:none;}#yiv5235546321 div.yiv5235546321photo-title a,
#yiv5235546321 div.yiv5235546321photo-title a:active, #yiv5235546321
div.yiv5235546321photo-title a:hover, #yiv5235546321
div.yiv5235546321photo-title a:visited {text-decoration:none;}#yiv5235546321
div#yiv5235546321ygrp-mlmsg #yiv5235546321ygrp-msg p a
span.yiv5235546321yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv5235546321
.yiv5235546321green {color:#628c2a;}#yiv5235546321 .yiv5235546321MsoNormal
{margin:0 0 0 0;}#yiv5235546321 o {font-size:0;}#yiv5235546321
#yiv5235546321photos div {float:left;width:72px;}#yiv5235546321
#yiv5235546321photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv5235546321
#yiv5235546321photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv5235546321
#yiv5235546321reco-category {font-size:77%;}#yiv5235546321
#yiv5235546321reco-desc {font-size:77%;}#yiv5235546321 .yiv5235546321replbq
{margin:4px;}#yiv5235546321 #yiv5235546321ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv5235546321 #yiv5235546321ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv5235546321
#yiv5235546321ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv5235546321
#yiv5235546321ygrp-mlmsg select, #yiv5235546321 input, #yiv5235546321 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv5235546321
#yiv5235546321ygrp-mlmsg pre, #yiv5235546321 code {font:115%
monospace;}#yiv5235546321 #yiv5235546321ygrp-mlmsg *
{line-height:1.22em;}#yiv5235546321 #yiv5235546321ygrp-mlmsg #yiv5235546321logo
{padding-bottom:10px;}#yiv5235546321 #yiv5235546321ygrp-msg p a
{font-family:Verdana;}#yiv5235546321 #yiv5235546321ygrp-msg
p#yiv5235546321attach-count span {color:#1E66AE;font-weight:700;}#yiv5235546321
#yiv5235546321ygrp-reco #yiv5235546321reco-head
{color:#ff7900;font-weight:700;}#yiv5235546321 #yiv5235546321ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv5235546321 #yiv5235546321ygrp-sponsor
#yiv5235546321ov li a {font-size:130%;text-decoration:none;}#yiv5235546321
#yiv5235546321ygrp-sponsor #yiv5235546321ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv5235546321
#yiv5235546321ygrp-sponsor #yiv5235546321ov ul {margin:0;padding:0 0 0
8px;}#yiv5235546321 #yiv5235546321ygrp-text
{font-family:Georgia;}#yiv5235546321 #yiv5235546321ygrp-text p {margin:0 0 1em
0;}#yiv5235546321 #yiv5235546321ygrp-text tt {font-size:120%;}#yiv5235546321
#yiv5235546321ygrp-vital ul li:last-child {border-right:none
!important;}#yiv5235546321
