Copy data otomastis

  1. tahun lalu
    Di sunting tahun lalu oleh Hamzah

    Pagi Master Master Excel,

    Mohon bantuannya untuk kode VBA utk case berikut:

    Misalkan ada file Master.xlsx dan Toko A-F.xlsx,
    bagaimana cara utk mengcopy otomatis data dari master range C3:G7 ke masing2 file toko tersebut.

    Note:
    Misalkan File Toko tersebut terprotect (sheet) pass: 123

    Apabila ada pertanyaan yg belum jelas, mohon diluruskan.

    Sebelumnya saya ucapkan terima kasih.

    Salam,
    Hamzah

    TOKO A.xlsxMASTER.xlsxTOKO B.xlsx

    @Hamzah ...

    ... sudah saya coba kenapa isinya sama semua ya ...

    Sama bagaimana mas? Di sheet MASTER pada file MASTER.xlsm range C3:G7 khan sudah terisi formula :

    =$A$1&1

    yang jika disalin ke Workbook berbeda (misalkan ke file TOKO A.xlsx s.d. TOKO C.xlsx), hasilnya akan mengikuti nilai sel A1 di masing-masing file target. Misalkan untuk file TOKO B.xlsx, hasilnya akan menjadi seperti berikut :

    [attachment:62ceda58a2ae1]

    Jadi tidak perlu mengganti nilai sel A1 di sheet MASTER file MASTER.xlsm.

    ... yang saya mau ganti A, copy, paste ke toko a.xlsx ...

    Saya hanya mengikuti penjelasan awal Anda. Tidak ada penjelasan ganti ini itu sebelumnya. Lagian, dengan formula di atas, sudah otomatis mengubah nilai pada masing-masing file target.

    Saya tidak tahu apakah Validation List pada sel A1 sheet Master akan berisi nama file target atau apa. Anda tidak menjelaskannya. Jadi saya asumsikan saja isinya adalah nama file target. Untuk perubahan scriptnya bisa seperti berikut :

    Public Sub CopyData()
        Dim xlWB As Workbook, xlWS As Worksheet
        Dim vIDX As Variant
        Dim sFN As String
        Dim lIdx As Long
        
        Err.Clear: On Error GoTo errHandler
        
        '+-- Salin data dari validation list.
        vIDX = Evaluate(Sheet1.Range("A1").Validation.Formula1)
        vIDX = Application.Transpose(vIDX)
        
        Err.Clear: On Error GoTo 0
        
        Application.ScreenUpdating = False
        
        For lIdx = LBound(vIDX) To UBound(vIDX)
            If Len(vIDX(lIdx)) Then
                '+-- Tetapkan nama file target.
                sFN = Replace("TOKO {X}.xlsx", "{X}", vIDX(lIdx))
                sFN = ThisWorkbook.Path & "\" & sFN
                
                '+-- Periksa apakah file exist?
                If Dir(sFN) <> vbNullString Then
                    '+-- Buka file target dan tetapkan ke object xlWB.
                    Set xlWB = ThisWorkbook.Application.Workbooks.Open(Filename:=sFN, UpdateLinks:=False)
                
                    '+-- Tetapkan object xlWS ke sheet pertama.
                    Set xlWS = xlWB.Worksheets(1)
                    '+-- Buka proteksi sheet.
                    xlWS.Unprotect "123"
                
                    '+-- Ubah kode indeks di sel A1.
                    ThisWorkbook.Worksheets(1).Range("A1") = vIDX(lIdx)
                    '+-- Salin data dari file master.
                    ThisWorkbook.Worksheets(1).Range("C3:G7").Copy
                
                    '+-- Tempel value ke range yang sama di sheet target.
                    xlWS.Range("C3:G7").PasteSpecial xlPasteValues
                    '+-- Pindahkan pointer ke sel A1.
                    xlWS.Range("A1").Select
                    '+-- Proteksi kembali sheet target.
                    xlWS.Protect Password:="123", UserInterfaceOnly:=True
                
                    '+-- Hapus referensi ke sheet aktif.
                    Set xlWS = Nothing
                    '+-- Tutup file target.
                    xlWB.Close True
                End If
            End If
        Next
    
    errHandler:
        If Err Then
            MsgBox "Error getting validation list data!"
            Err.Clear: On Error GoTo 0
        End If
        
        Application.ScreenUpdating = True
    End Sub

    Silahkan disesuaikan dengan target aktualnya.

    Demikian.

  2. Caton

    13 Jul 2022 Terverifikasi Indonesia + 19.826 Poin

    @Hamzah ...

    Terlampir contoh sederhana script VBA-nya. Semoga sesuai.

    Demikian.

  3. Di sunting tahun lalu oleh Hamzah

    Alhamdulillah direspon langsung master caton.

    Terima Kasih bantuannya.

    Pertanyaan lainnya:
    sudah saya coba kenapa isinya sama semua ya,
    yang saya mau

    • ganti A, copy, paste ke toko a.xlsx
    • ganti B, copy, paste ke toko b.lxsx

    ..

    Option Explicit

    Public Sub CopyData()
    Dim vFN As Variant
    Dim lIdx As Long
    Dim xlWB As Workbook, xlWS As Worksheet

    Application.ScreenUpdating = False

    '+-- Daftar nama file target.
    vFN = Array("TOKO A.xlsx", "TOKO B.xlsx", "TOKO C.xlsx")

    For lIdx = 0 To 2
    '+-- Tetapkan nama file target.
    vFN(lIdx) = "d:\toko\" & vFN(lIdx)
    '+-- Buka file target dan tetapkan ke object xlWB.
    Set xlWB = ThisWorkbook.Application.Workbooks.Open(Filename:=vFN(lIdx), UpdateLinks:=False)

    Set xlWS = xlWB.Worksheets(1) '+-- Tetapkan object xlWS ke sheet pertama.
    xlWS.Unprotect "123" '+-- Buka proteksi sheet.
    ThisWorkbook.Worksheets(1).Range("C3:G7").Copy '+-- Salin data dari file master.
    xlWS.Range("C3:G7").PasteSpecial xlPasteValues '+-- Tempel ke range yang sama di sheet target.
    xlWS.Range("A1").Select '+-- Pindahkan pointer ke sel A1.
    xlWS.Protect Password:="123", UserInterfaceOnly:=True '+-- Proteksi kembali sheet target.

    Set xlWS = Nothing '+-- Hapus referensi ke sheet aktif.
    xlWB.Close True '+-- Tutup file target.
    Next

    Application.ScreenUpdating = True
    End Sub

    Moon bantuannya
    karena isi file nya nanti saya sesuaikan dan akan saya apdet. jd biar hasil pastenya masing2 beda

  4. Caton

    13 Jul 2022 Terverifikasi Jawaban Terpilih Indonesia + 19.826 Poin

    @Hamzah ...

    ... sudah saya coba kenapa isinya sama semua ya ...

    Sama bagaimana mas? Di sheet MASTER pada file MASTER.xlsm range C3:G7 khan sudah terisi formula :

    =$A$1&1

    yang jika disalin ke Workbook berbeda (misalkan ke file TOKO A.xlsx s.d. TOKO C.xlsx), hasilnya akan mengikuti nilai sel A1 di masing-masing file target. Misalkan untuk file TOKO B.xlsx, hasilnya akan menjadi seperti berikut :

    image_2022-07-13_214509916.png

    Jadi tidak perlu mengganti nilai sel A1 di sheet MASTER file MASTER.xlsm.

    ... yang saya mau ganti A, copy, paste ke toko a.xlsx ...

    Saya hanya mengikuti penjelasan awal Anda. Tidak ada penjelasan ganti ini itu sebelumnya. Lagian, dengan formula di atas, sudah otomatis mengubah nilai pada masing-masing file target.

    Saya tidak tahu apakah Validation List pada sel A1 sheet Master akan berisi nama file target atau apa. Anda tidak menjelaskannya. Jadi saya asumsikan saja isinya adalah nama file target. Untuk perubahan scriptnya bisa seperti berikut :

    Public Sub CopyData()
        Dim xlWB As Workbook, xlWS As Worksheet
        Dim vIDX As Variant
        Dim sFN As String
        Dim lIdx As Long
        
        Err.Clear: On Error GoTo errHandler
        
        '+-- Salin data dari validation list.
        vIDX = Evaluate(Sheet1.Range("A1").Validation.Formula1)
        vIDX = Application.Transpose(vIDX)
        
        Err.Clear: On Error GoTo 0
        
        Application.ScreenUpdating = False
        
        For lIdx = LBound(vIDX) To UBound(vIDX)
            If Len(vIDX(lIdx)) Then
                '+-- Tetapkan nama file target.
                sFN = Replace("TOKO {X}.xlsx", "{X}", vIDX(lIdx))
                sFN = ThisWorkbook.Path & "\" & sFN
                
                '+-- Periksa apakah file exist?
                If Dir(sFN) <> vbNullString Then
                    '+-- Buka file target dan tetapkan ke object xlWB.
                    Set xlWB = ThisWorkbook.Application.Workbooks.Open(Filename:=sFN, UpdateLinks:=False)
                
                    '+-- Tetapkan object xlWS ke sheet pertama.
                    Set xlWS = xlWB.Worksheets(1)
                    '+-- Buka proteksi sheet.
                    xlWS.Unprotect "123"
                
                    '+-- Ubah kode indeks di sel A1.
                    ThisWorkbook.Worksheets(1).Range("A1") = vIDX(lIdx)
                    '+-- Salin data dari file master.
                    ThisWorkbook.Worksheets(1).Range("C3:G7").Copy
                
                    '+-- Tempel value ke range yang sama di sheet target.
                    xlWS.Range("C3:G7").PasteSpecial xlPasteValues
                    '+-- Pindahkan pointer ke sel A1.
                    xlWS.Range("A1").Select
                    '+-- Proteksi kembali sheet target.
                    xlWS.Protect Password:="123", UserInterfaceOnly:=True
                
                    '+-- Hapus referensi ke sheet aktif.
                    Set xlWS = Nothing
                    '+-- Tutup file target.
                    xlWB.Close True
                End If
            End If
        Next
    
    errHandler:
        If Err Then
            MsgBox "Error getting validation list data!"
            Err.Clear: On Error GoTo 0
        End If
        
        Application.ScreenUpdating = True
    End Sub

    Silahkan disesuaikan dengan target aktualnya.

    Demikian.

  5. Sudah sesuai yang dibutuhkan.

    Terima Kasih Master @Caton

  6. Izin menyambung pertanyaan lain Master @Caton ,

    Kalau kode VBA utk save as dan rename. bagaimana ya?

    dengan file Master diatas,

    • ganti list A, protect sheet pass123, Save as Toko A.xlsx
    • ganti list B, protect sheet pass123, Save as Toko A.xlsx
    • s/d F

    Demikian dan terima kasih.

  7. Caton

    15 Jul 2022 Terverifikasi Indonesia + 19.826 Poin

    ... kalau kode VBA utk save as dan rename ...

    Baca referensi untuk SaveAs di sini , dan referensi untuk Rename di sini . Untuk penerapan pada script di atas, untuk SaveAs bisa dilakukan sebelum proses menutup file target, misalkan :

    ...
    
     '+-- Tutup file target.
    xlWB.SaveAs sFN
    xlWB.Close
    
    ...
     

    Sedangkan untuk Rename, tergantung kondisinya. Pada dasarnya, perintah SaveAs di atas juga sudah mencukupi. Kalau mau menerapkan fungsi Rename, pastikan file target dalam kondisi tertutup agar tidak menimbulkan error. Misalkan saja :

    ...
    
     '+-- Tutup file target.
    xlWB.Close True
    
    '+-- Rename file target.
    sNamaBaru = ThisWorkbook.Path & "\FileData " & lIdx & ".xlsx"
    Name sFN As sNamaBaru
    
    ...
     

    Demikian.

  8. Siap master @Caton saya coba dulu ya,

    nanti saya tanyakan lagi jika ada kendala.

    Thanks.

 

atau Mendaftar untuk ikut berdiskusi!