Hai Rubi,Coba ubah codenya menjadi seperti ini :
ThisWorkbook.Sheets("Laporan").Range("B7:F1000").Clear
ThisWorkbook.Sheets("Data").Range("A1").CurrentRegion. _ AdvancedFilter
Action:=xlFilterCopy, _
CriteriaRange:=ThisWorkbook.Sheets("Laporan").Range("C3:D4"), _
CopyToRange:=ThisWorkbook.Sheets("Laporan").Range("B6:F6"), _
Unique:=False
ThisWorkbook.Sheets("Laporan").Range("B6").End(xlDown).SelectWith Selection
.Offset(5, 0).Value = "Yang Membuat" .Offset(5, 2).Value = "Mengetahui"
.Offset(5, 4).Value = "Menyetujui" .Offset(9, 0).Value = "TTD1"
.Offset(9, 2).Value = "TTD2" .Offset(9, 4).Value = "TTD3"End With
catatan : Huruf yang dicetak tebal adalah tambahan codenya
Salam,HK
From: "rubi anto [email protected] [belajar-excel]"
<[email protected]>
To: [email protected]
Sent: Wednesday, 1 February 2017, 12:58
Subject: Re: [belajar-excel] Tabel tanda tangan dalam Advanced Filter [1
Attachment]
Apakah seperti data berikut Mr Kid....
Tapi jika ada data bertambah malah tertindis...
Maaf saya masih belum Faham Mr. Kid.....
Pada 31 Januari 2017 20.42, 'Mr. Kid' [email protected] [belajar-excel]
<[email protected]> menulis:
Wa'alaikumussalam wr wb
Hai Rubi,
Bisa membuat template tanda tangan di tempat lain (misal di kolom ZZ dst).
Kemudian copy range area template tanda tangan tersebut ke bawah tabel hasil
advanced filter.
Kalau proses copy template tanda tangan akan diotomasi, bisa dilakukan dengan
record macro.
Cara meletakkan posisi cells aktif ke baris kosong kedua di bawah tabel pada
kolom terkiri tabel adalah sebagai berikut : (asumsi : kolom pertama tabel
selalu terisi penuh)
1. klik cells baris 1 pada sebuah kolom kosong
2. tekan CTRL panah ke bawah -> tekan CTRL panah ke kiri ::: sampai di
kolom A baris terakhir Excel
3. [jika kolom pertama tabel bukan di kolom A] tekan panah ke kanan sampai ke
kolom pertama tabel atau sampai kolom tabel yang selalu terisi penuh
4. tekan CTRL panah ke atas ::: sampai di baris terakhir tabel
5. tekan panah ke bawah 2 kali ::: sampai baris kosong kedua di kolom tabel
yang selalu terisi penuh
6. [jika kolom pertama tabel bukan kolom yang selalu terisi penuh] tekan CTRL
panah kiri atau kanan sampai ke kolom pertama tabel
Jadi urutan copy paste template tanda tangan menjadi :
>> blok area template tanda tangan -> tekan CTRL C -> ikuti langkah 1 sampai 6
>> di atas -> tekan CTRL V
Wassalamu'alaikum wr wb
Kid
2017-01-31 18:37 GMT+07:00 rubi anto [email protected] [belajar-excel]
<[email protected] >:
Assalamu'alaikum.....
Para master excel.....
Saya sedang belajar membuat data laporan pembayaran dengan advanced filter
macro,tetapi ada kendala....
kolom untuk tanda tangannya selalu saja hilang jika saya klik button filter....
Bagaimanakah caranya agar kolom tanda tangan itu tidak hilang dan selalu berada
di bawah tabel data jika data bertambah atau berkurang....
Terimakasih sebelumnya atas bantuan para guru.....
(File terlampir)
salamRubi
#yiv7157507930 #yiv7157507930 -- #yiv7157507930ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv7157507930
#yiv7157507930ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv7157507930
#yiv7157507930ygrp-mkp #yiv7157507930hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv7157507930 #yiv7157507930ygrp-mkp #yiv7157507930ads
{margin-bottom:10px;}#yiv7157507930 #yiv7157507930ygrp-mkp .yiv7157507930ad
{padding:0 0;}#yiv7157507930 #yiv7157507930ygrp-mkp .yiv7157507930ad p
{margin:0;}#yiv7157507930 #yiv7157507930ygrp-mkp .yiv7157507930ad a
{color:#0000ff;text-decoration:none;}#yiv7157507930 #yiv7157507930ygrp-sponsor
#yiv7157507930ygrp-lc {font-family:Arial;}#yiv7157507930
#yiv7157507930ygrp-sponsor #yiv7157507930ygrp-lc #yiv7157507930hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv7157507930
#yiv7157507930ygrp-sponsor #yiv7157507930ygrp-lc .yiv7157507930ad
{margin-bottom:10px;padding:0 0;}#yiv7157507930 #yiv7157507930actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv7157507930
#yiv7157507930activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv7157507930
#yiv7157507930activity span {font-weight:700;}#yiv7157507930
#yiv7157507930activity span:first-child
{text-transform:uppercase;}#yiv7157507930 #yiv7157507930activity span a
{color:#5085b6;text-decoration:none;}#yiv7157507930 #yiv7157507930activity span
span {color:#ff7900;}#yiv7157507930 #yiv7157507930activity span
.yiv7157507930underline {text-decoration:underline;}#yiv7157507930
.yiv7157507930attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv7157507930 .yiv7157507930attach div a
{text-decoration:none;}#yiv7157507930 .yiv7157507930attach img
{border:none;padding-right:5px;}#yiv7157507930 .yiv7157507930attach label
{display:block;margin-bottom:5px;}#yiv7157507930 .yiv7157507930attach label a
{text-decoration:none;}#yiv7157507930 blockquote {margin:0 0 0
4px;}#yiv7157507930 .yiv7157507930bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv7157507930
.yiv7157507930bold a {text-decoration:none;}#yiv7157507930 dd.yiv7157507930last
p a {font-family:Verdana;font-weight:700;}#yiv7157507930 dd.yiv7157507930last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv7157507930
dd.yiv7157507930last p span.yiv7157507930yshortcuts
{margin-right:0;}#yiv7157507930 div.yiv7157507930attach-table div div a
{text-decoration:none;}#yiv7157507930 div.yiv7157507930attach-table
{width:400px;}#yiv7157507930 div.yiv7157507930file-title a, #yiv7157507930
div.yiv7157507930file-title a:active, #yiv7157507930
div.yiv7157507930file-title a:hover, #yiv7157507930 div.yiv7157507930file-title
a:visited {text-decoration:none;}#yiv7157507930 div.yiv7157507930photo-title a,
#yiv7157507930 div.yiv7157507930photo-title a:active, #yiv7157507930
div.yiv7157507930photo-title a:hover, #yiv7157507930
div.yiv7157507930photo-title a:visited {text-decoration:none;}#yiv7157507930
div#yiv7157507930ygrp-mlmsg #yiv7157507930ygrp-msg p a
span.yiv7157507930yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv7157507930
.yiv7157507930green {color:#628c2a;}#yiv7157507930 .yiv7157507930MsoNormal
{margin:0 0 0 0;}#yiv7157507930 o {font-size:0;}#yiv7157507930
#yiv7157507930photos div {float:left;width:72px;}#yiv7157507930
#yiv7157507930photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv7157507930
#yiv7157507930photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv7157507930
#yiv7157507930reco-category {font-size:77%;}#yiv7157507930
#yiv7157507930reco-desc {font-size:77%;}#yiv7157507930 .yiv7157507930replbq
{margin:4px;}#yiv7157507930 #yiv7157507930ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv7157507930 #yiv7157507930ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv7157507930
#yiv7157507930ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv7157507930
#yiv7157507930ygrp-mlmsg select, #yiv7157507930 input, #yiv7157507930 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv7157507930
#yiv7157507930ygrp-mlmsg pre, #yiv7157507930 code {font:115%
monospace;}#yiv7157507930 #yiv7157507930ygrp-mlmsg *
{line-height:1.22em;}#yiv7157507930 #yiv7157507930ygrp-mlmsg #yiv7157507930logo
{padding-bottom:10px;}#yiv7157507930 #yiv7157507930ygrp-msg p a
{font-family:Verdana;}#yiv7157507930 #yiv7157507930ygrp-msg
p#yiv7157507930attach-count span {color:#1E66AE;font-weight:700;}#yiv7157507930
#yiv7157507930ygrp-reco #yiv7157507930reco-head
{color:#ff7900;font-weight:700;}#yiv7157507930 #yiv7157507930ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv7157507930 #yiv7157507930ygrp-sponsor
#yiv7157507930ov li a {font-size:130%;text-decoration:none;}#yiv7157507930
#yiv7157507930ygrp-sponsor #yiv7157507930ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv7157507930
#yiv7157507930ygrp-sponsor #yiv7157507930ov ul {margin:0;padding:0 0 0
8px;}#yiv7157507930 #yiv7157507930ygrp-text
{font-family:Georgia;}#yiv7157507930 #yiv7157507930ygrp-text p {margin:0 0 1em
0;}#yiv7157507930 #yiv7157507930ygrp-text tt {font-size:120%;}#yiv7157507930
#yiv7157507930ygrp-vital ul li:last-child {border-right:none
!important;}#yiv7157507930
