Yang anda inginkan sebenarnya seperti apa...Coba kirim filenya lagi dan hasil 
yang anda inginkan semuanya biar teman-teman disini juga mengerti..

TerimaKasih
 

    Pada Rabu, 11 April 2018 6:52, "Cari Kebenaran cahayailm...@gmail.com 
[belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
 

     Pak Toby Kenzo, terima kasih atas responnya. 
Code saya ubah jadi seperti berikut ini:
Private Sub Worksheet_Change(ByVal Target As Range)If Not 
Application.Intersect(Range("L13:L17"), Range(Target..Address)) Is Nothing 
ThenIf Range(Target.Address) > 0 
ThenWorksheets("INVOICE").Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 
-2)).CopyWorksheets("INVOICE").Cells(21, 1).End(xlUp).Offset(1, 0).PasteSpecial 
xlPasteValuesApplication.CutCopyMode = FalseRange(Target.Address).Offset(0, -1) 
= ""Range(Target.Address).Offset(0, 0) = 
""Worksheets("INVOICE").Range("K12").SelectEnd IfEnd IfEnd Sub

Yang belum terpecahkan adalah: apabila  baris ke 21 sudah terisi, pinginnya 
aktivitas copy paste berhenti karena sudah tidak tempat kosong lagi. Ini 
sebagai antisipasi salah input . Code VBA ini yang masih belum bisa saya susun. 
Terima kasih 
On Tue, Apr 10, 2018 at 4:55 PM, Toby kenzo tobyke...@yahoo.co.id 
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:

     Coba ganti scriptnya :

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo 0If Not Intersect(Target, Range("L13:L17")) IsNothing 
ThenSheet1.Range("A" & (Target.Row - 6)).Value =Cells(Target.Row, Target.Column 
- 2).ValueSheet1.Range("B" & (Target.Row - 6)).Value =Cells(Target.Row, 
Target.Column - 1).ValueSheet1.Range("C" & (Target.Row - 6)).Value 
=Cells(Target.Row, Target.Column).ValueOn Error Resume NextIf Target.Value < 7 
ThenSheet1.Range("A" & (Target.Row - 6)).Value =""Sheet1.Range("B" & 
(Target..Row - 6)).Value =""Sheet1.Range("C" & (Target.Row - 6)).Value =""If 
Target.Value = "" ThenSheet1.Range("A" & (Target.Row - 6)).Value 
=""Sheet1.Range("B" & (Target.Row - 6))..Value =""Sheet1.Range("C" & 
(Target.Row - 6)).Value =""ElseEnd IfEnd IfEnd IfEnd Sub

TerimaKasih





 

    Pada Senin, 9 April 2018 14:15, "Cari Kebenaran cahayailm...@gmail.com 
[belajar-excel]" <belajar-excel@yahoogroups.com > menulis:
 

     Assalamu'alaikum warahmatullahi wabarakatuh
Kepada Para Master dan segenap anggota
Saya ingin mengcopy data antar cell dalam worksheet dengan ketentuan sebagai 
berikut: 
apabila sayamengetikkan nilai di atas 6 pada salah satu cell yang ada di range 
L13 sampaidengan L17, maka dari cell yang bersangkutan berikut 2 celldisebelah 
kanannya akan tercopy (VALUE) secara otomatis ke range A7sampai C7 (dibawah 
baris yang sudah terisi data).  Dan apabila baris A21 sampai dengan C7 sudah 
terisi data(record), maka perintah (code VBA) tidak boleh dijalankan lagi. VBA 
Code yangada cuma bisa di 1 cell saja , yaitu cell "L13".
Atas perhatian dan bantuannya, saya mengucapkan terima kasih
Wassalamu'alaikum warahmatullahi wabarakatuh 


  

      

  #yiv5868833588 #yiv5868833588 -- #yiv5868833588ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5868833588 
#yiv5868833588ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5868833588 
#yiv5868833588ygrp-mkp #yiv5868833588hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv5868833588 #yiv5868833588ygrp-mkp #yiv5868833588ads 
{margin-bottom:10px;}#yiv5868833588 #yiv5868833588ygrp-mkp .yiv5868833588ad 
{padding:0 0;}#yiv5868833588 #yiv5868833588ygrp-mkp .yiv5868833588ad p 
{margin:0;}#yiv5868833588 #yiv5868833588ygrp-mkp .yiv5868833588ad a 
{color:#0000ff;text-decoration:none;}#yiv5868833588 #yiv5868833588ygrp-sponsor 
#yiv5868833588ygrp-lc {font-family:Arial;}#yiv5868833588 
#yiv5868833588ygrp-sponsor #yiv5868833588ygrp-lc #yiv5868833588hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv5868833588 
#yiv5868833588ygrp-sponsor #yiv5868833588ygrp-lc .yiv5868833588ad 
{margin-bottom:10px;padding:0 0;}#yiv5868833588 #yiv5868833588actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv5868833588 
#yiv5868833588activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv5868833588
 #yiv5868833588activity span {font-weight:700;}#yiv5868833588 
#yiv5868833588activity span:first-child 
{text-transform:uppercase;}#yiv5868833588 #yiv5868833588activity span a 
{color:#5085b6;text-decoration:none;}#yiv5868833588 #yiv5868833588activity span 
span {color:#ff7900;}#yiv5868833588 #yiv5868833588activity span 
.yiv5868833588underline {text-decoration:underline;}#yiv5868833588 
.yiv5868833588attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv5868833588 .yiv5868833588attach div a 
{text-decoration:none;}#yiv5868833588 .yiv5868833588attach img 
{border:none;padding-right:5px;}#yiv5868833588 .yiv5868833588attach label 
{display:block;margin-bottom:5px;}#yiv5868833588 .yiv5868833588attach label a 
{text-decoration:none;}#yiv5868833588 blockquote {margin:0 0 0 
4px;}#yiv5868833588 .yiv5868833588bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv5868833588 
.yiv5868833588bold a {text-decoration:none;}#yiv5868833588 dd.yiv5868833588last 
p a {font-family:Verdana;font-weight:700;}#yiv5868833588 dd.yiv5868833588last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv5868833588 
dd.yiv5868833588last p span.yiv5868833588yshortcuts 
{margin-right:0;}#yiv5868833588 div.yiv5868833588attach-table div div a 
{text-decoration:none;}#yiv5868833588 div.yiv5868833588attach-table 
{width:400px;}#yiv5868833588 div.yiv5868833588file-title a, #yiv5868833588 
div.yiv5868833588file-title a:active, #yiv5868833588 
div.yiv5868833588file-title a:hover, #yiv5868833588 div.yiv5868833588file-title 
a:visited {text-decoration:none;}#yiv5868833588 div.yiv5868833588photo-title a, 
#yiv5868833588 div.yiv5868833588photo-title a:active, #yiv5868833588 
div.yiv5868833588photo-title a:hover, #yiv5868833588 
div.yiv5868833588photo-title a:visited {text-decoration:none;}#yiv5868833588 
div#yiv5868833588ygrp-mlmsg #yiv5868833588ygrp-msg p a 
span.yiv5868833588yshortcuts 
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv5868833588 
.yiv5868833588green {color:#628c2a;}#yiv5868833588 .yiv5868833588MsoNormal 
{margin:0 0 0 0;}#yiv5868833588 o {font-size:0;}#yiv5868833588 
#yiv5868833588photos div {float:left;width:72px;}#yiv5868833588 
#yiv5868833588photos div div {border:1px solid 
#666666;min-height:62px;overflow:hidden;width:62px;}#yiv5868833588 
#yiv5868833588photos div label 
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv5868833588
 #yiv5868833588reco-category {font-size:77%;}#yiv5868833588 
#yiv5868833588reco-desc {font-size:77%;}#yiv5868833588 .yiv5868833588replbq 
{margin:4px;}#yiv5868833588 #yiv5868833588ygrp-actbar div a:first-child 
{margin-right:2px;padding-right:5px;}#yiv5868833588 #yiv5868833588ygrp-mlmsg 
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv5868833588 
#yiv5868833588ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv5868833588 
#yiv5868833588ygrp-mlmsg select, #yiv5868833588 input, #yiv5868833588 textarea 
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv5868833588 
#yiv5868833588ygrp-mlmsg pre, #yiv5868833588 code {font:115% 
monospace;}#yiv5868833588 #yiv5868833588ygrp-mlmsg * 
{line-height:1.22em;}#yiv5868833588 #yiv5868833588ygrp-mlmsg #yiv5868833588logo 
{padding-bottom:10px;}#yiv5868833588 #yiv5868833588ygrp-msg p a 
{font-family:Verdana;}#yiv5868833588 #yiv5868833588ygrp-msg 
p#yiv5868833588attach-count span {color:#1E66AE;font-weight:700;}#yiv5868833588 
#yiv5868833588ygrp-reco #yiv5868833588reco-head 
{color:#ff7900;font-weight:700;}#yiv5868833588 #yiv5868833588ygrp-reco 
{margin-bottom:20px;padding:0px;}#yiv5868833588 #yiv5868833588ygrp-sponsor 
#yiv5868833588ov li a {font-size:130%;text-decoration:none;}#yiv5868833588 
#yiv5868833588ygrp-sponsor #yiv5868833588ov li 
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv5868833588 
#yiv5868833588ygrp-sponsor #yiv5868833588ov ul {margin:0;padding:0 0 0 
8px;}#yiv5868833588 #yiv5868833588ygrp-text 
{font-family:Georgia;}#yiv5868833588 #yiv5868833588ygrp-text p {margin:0 0 1em 
0;}#yiv5868833588 #yiv5868833588ygrp-text tt {font-size:120%;}#yiv5868833588 
#yiv5868833588ygrp-vital ul li:last-child {border-right:none 
!important;}#yiv5868833588 

   
  • [belajar-excel] Copy... Cari Kebenaran cahayailm...@gmail.com [belajar-excel]
    • Bls: [belajar-e... Toby kenzo tobyke...@yahoo.co.id [belajar-excel]
      • Re: [belaja... Cari Kebenaran cahayailm...@gmail.com [belajar-excel]
        • Bls: [b... Toby kenzo tobyke...@yahoo.co.id [belajar-excel]
          • Re:... Cari Kebenaran cahayailm...@gmail.com [belajar-excel]
            • ... 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]
    • Re: [belajar-ex... Sony Ario sony.ar...@gmail.com [belajar-excel]
    • Re: [belajar-ex... 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]

Kirim email ke