Copy data looping

Question

I try to copy data from table tblkasir to tbldafta using a button. But when I click the button the button my Excel is not responding and keeps adding the data until the last row.

Here the script of the button.

Private Sub cmdSimpan_Click()
    SimpanNota
    SimpanDafta
End Sub


Sub SimpanNota()
    ActiveWorkbook.Sheets("kasir").Activate
    Sheets("kasir").Range("Q4").Select

    Do
        If IsEmpty(ActiveCell) = False Then
            ActiveCell.Offset(1, 0).Select
        End If
    Loop Until IsEmpty(ActiveCell) = True

    ActiveCell.Value = Sheets("kasir").Range("N3").Value
End Sub


Sub SimpanDafta() 'this is the script that keep looping
    ActiveWorkbook.Sheets("daftar transaksi").Activate
    Sheets("daftar transaksi").Range("tbldafta[[nomor transaksi]:[jumlah]]").Select

    Do
        If IsEmpty(ActiveCell) = False Then
            ActiveCell.Offset(1, 0).Select
        End If
    Loop Until IsEmpty(ActiveCell) = True

    Sheets("daftar transaksi").Select
    Sheets("kasir").Range("tblkasir[[nomor transaksi]:[jumlah]]").Copy Destination:=Sheets("daftar transaksi").Range(Selection, Selection.End(xlDown))
    ActiveCell.Offset(1, 1).Range("C9") = Sheets("daftar transaksi").Range(Selection, Selection.End(xlDown))
    Application.CutCopyMode = False
    Sheets("kasir").Range("tblkasir[[nomor transaksi]:[jumlah]]").ClearContents
    Sheets("kasir").Range("K4").ClearContents
End Sub

sheets daftar transaksi

sheets kasir

please help.

Your problem is not clear: Does the code run, when you click the button? Furthermore I recommend reading [How to avoid using select](https://stackoverflow.com/a/23913882/16578424) and [How to avoid copy/pase](https://stackoverflow.com/a/64611707/16578424) — Ike, Nov 16 '22 at 06:49
Yes, the code is working. But keep adding the same line until the last row in sheet data transaksi — Uzin21, Nov 16 '22 at 07:11
In addition I recommend to read [The VBA Guide To ListObject Excel Tables](https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables). This is the way you should work with tables. — Pᴇʜ, Nov 16 '22 at 07:11
Using `Select` and `Activate` is always an issue because it is not reliable and therefore leads to many issues. Get rid of them first. — Pᴇʜ, Nov 16 '22 at 07:13
Thank you. I will read the link that you gave first. Im new to this, and only followed the tutorial that i watched . — Uzin21, Nov 16 '22 at 07:44

score 0 · Answer 1 · answered Nov 16 '22 at 08:48

Not only is it advised to avoid Select while copying and pasting, but you also seem to be reinventing the "Select Current Region" feature :-)

Just for giving you an idea about this, I've made this very simple macro, using "Select current region" as a simpler way to handle entire tables:

(I've created two sheets, called "TblSource" and "TblDest")

Sheets("TblSource").Range("A1").CurrentRegion.Copy
Sheets("TblDest").Select
Range("A1").Select
ActiveSheet.Paste

score 0 · Accepted Answer · edited Jan 13 '23 at 06:56

Im using this way to do it. thank you for your recommendation...

'COPY DATA DARI SHEET KASIR KE SHEET DAFTAR TRANSAKSI
Sub SimpanDafta()
    'PILIH SHEET YANG AKAN DICOPY, YAITU SHEET : KASIR
    ActiveSheet.ListObjects("tblkasir").ListColumns(2).DataBodyRange.Resize(, 9).Select
    Selection.Copy

    'PILIH SHEET YANG AKAN DIPASTE, YAITU SHEET : DAFTAR TRANSAKSI
    Sheets("daftar transaksi").Select
    Do
        If IsEmpty(ActiveCell) = False Then
            ActiveCell.Offset(1, 0).Select
        End If
    Loop Until IsEmpty(ActiveCell) = True
    ActiveSheet.Paste
End Sub

Copy data looping

2 Answers2