Copy spesifik sheet ke workbook yang berbeda

  1. 9 bulan yang lalu

    Saya mencoba membuat program yang dapat meng-copy spesifik sheet (dalam hal ini "UT") ke dalam master workbook dan berada pada satu folder.

    Public Sub myImportUT()
        Dim sPathName As String, sFileName As String
        Dim sourceWb As Workbook, targetWb As Workbook
    
        Application.ScreenUpdating = False
        Set sourceWb = ActiveWorkbook
        
        sPathName = ThisWorkbook.Path & "\"
        sFileName = Dir(sPathName & "*.xls", vbNormal)
        
        Do While Len(sFileName) > 0
            sFileName = sPathName & sFileName
            
            Set targetWb = Workbooks.Open(sFileName)
            targetWb.Sheets("UT").Copy After:=sourceWb.Sheets("My")
            targetWb.Close
            
            sFileName = Dir
        Loop
        
        Application.ScreenUpdating = True
    End Sub

    Yang menjadi pertanyan saya adalah, jika didalam folder tersebut memiliki file xls yang tidak mempunyai sheet "UT" maka program menghasilkan error 9 . Bagaimana cara penyelesaiannya?

    Saya mencoba membuat code dengan if ... then akan tetapi muncul error 438 .

    Mohon pencerahan. :D

  2. Caton

    8 Peb 2018 Terverifikasi Indonesia + 12.602 Poin

    Coba gunakan pengulangan FOR ... EACH untuk mendapatkan Sheet.Name dari setiap Worksheet pada Workbook yang dibuka. Bandingkan Sheet.Name dengan nama sheet yang dicari. Bila ditemukan, salin sheet tersebut lalu keluar dari pengulangan. Berikut modifikasi script VBA yang mas @Alan13 buat pada diskusi sebelumnya:

    Public Sub myImport()
        Dim sPathName As String, sFileName As String
        Dim sourceWb As Workbook, targetWb As Workbook
        Dim sh As Worksheet
        
        Set targetWb = ActiveWorkbook
        
        sPathName = ThisWorkbook.Path & "\"
        sFileName = Dir(sPathName & "*.xls", vbNormal)
        
        Do While Len(sFileName) > 0
            sFileName = sPathName & sFileName
            
            Set sourceWb = Workbooks.Open(sFileName)
            For Each sh In sourceWb.Worksheets
                If UCase$(Trim$(sh.Name)) = "UT" Then
                    sh.Copy After:=targetWb.Sheets("My")
                    Exit For
                End If
            Next
            sourceWb.Close
            
            sFileName = Dir
        Loop
    End Sub

    Mungkin demikian ide yang dapat diterapkan...

  3. Di sunting 9 bulan yang lalu oleh alan13

    Terima kasih mas @Caton , itu bekerja dengan baik.
    Tetapi masih ada muncul error 1004 .

    Ada juga sedikit peningkatan yang ingin saya buat, seperti:
    1. code sFileName = Dir(sPathName & "*.xls", vbNormal) ingin saya buat dapat membaca bukan hanya xls saja akan tetapi xlsx dan xlsm juga. Saya sudah searching, tetapi tidak menemukan cara yang berjalan dengan benar. Code yang saya buat sFileName = Dir(sPathName & "*.xls", vbNormal) tidak berjalan dengan baik.

    2. Berikutnya adalah saya mencoba membuat program tersebut dapat membaca setiap tipe excel, akan tetapi terkadang hal tersebut membuat dia membaca dan berusaha membuka diri dia sendiri yang membuat error. Saya mencoba membuat If targetWb <> sourceWb Then tetapi tidak berhasil.

    Mohon bantuannya kembali.

  4. Caton

    8 Peb 2018 Terverifikasi Indonesia + 12.602 Poin

    Coba modifikasi kembali scriptnya menjadi seperti berikut:

    Public Sub myImport()
        Dim sPathName As String, sFileName As String
        Dim sourceWb As Workbook, targetWb As Workbook
        Dim sh As Worksheet
        
        Set targetWb = ActiveWorkbook
        
        sPathName = ThisWorkbook.Path & "\"
        sFileName = Dir(sPathName & "*.xls*", vbNormal)
        
        Do While Len(sFileName) > 0
            sFileName = sPathName & sFileName
            If InStr(1, sFileName, targetWb.Name) = 0 Then
                Set sourceWb = Workbooks.Open(sFileName)
                For Each sh In sourceWb.Worksheets
                    If UCase$(Trim$(sh.Name)) = "UT" Then
                        sh.Copy After:=targetWb.Sheets("My")
                        Exit For
                    End If
                Next
                sourceWb.Close
            End If        
            sFileName = Dir
        Loop
    End Sub

    Demikian semoga sesuai... ;)

  5. Masalah untuk tidak membuka diri sendiri sudah terselesaikan, terima kasih mas @Caton .
    Berikutnya untuk pemecahan masalah error 1004 yang disebabkan mencoba meng-copy data yang masive/sangat besar. Menurut open source itu disebabkan ada sheet yang memiliki data masive yang saya coba copy. Jadi saya harus menyalin data yang lebih spesifik, tetapi itu bukan solusi untuk saya. Ada solusi yang lebih baik?

  6. Caton

    8 Peb 2018 Terverifikasi Indonesia + 12.602 Poin

    Sama-sama mas @Alan13... Untuk hal yang ditanyakan terakhir, sebenarnya informasi yang diberikan dari tautan tersebut sudah jelas:

    Untuk mengatasi masalah ini jika makro VBA Anda menyalin dan pasta kisaran 2,516 baris atau lebih baris, Ubah kode makro VBA untuk loop Salin dan tempel kecil kisaran data hingga kisaran yang akan disalin dan ditempel.

    Saya pernah menyusun proses yang sama (menyalin Worksheet dari Workbook berbeda), dengan jumlah data lebih dari 3000 baris dengan jumlah kolom 40 kolom. Untuk prosesnya, maka saya menyusun proses looping per n baris (n bisa 100, 500 atau 1000).

    Jika script di atas dikembangkan, sebaiknya dibuatkan prosedur khusus untuk menangani proses penyalinan data dengan pengulangan tersebut (opsional). Konsepnya:

    [1] Buka Workbook sumber (sesuai script di atas).

    [2] Periksa apakah sheet target ada pada file target. Jika ada, hitung jumlah kolom dan baris data. Misalkan:

    ...
    For Each sh In sourceWb.Worksheets
        If UCase$(Trim$(sh.Name)) = "UT" Then
            With sh
                lCols = Application.CountA(.Range("1:1"))
                lRows = Application.CountA(.Range("A1:A" & .Rows.Count))
                bValid = CBool(lCols * lRows) 
            End With
            
            If bValid Then
                '+-- Ubah nilai 2500 ke estimasi nilai yang tepat
                '+-- (baris yang bisa disalin secara langsung)
                If lRows <= 2500 Then
                    sh.Copy After:=targetWb.Sheets("My")
                Else
                    '+-- Susun proses penyalinan data manual.
                    '+-- Buat sheet baru [3]
                    '+-- Pengulangan proses menyalin data [4]
                End If
            End If
            Exit For
        End If
    Next
    ...

    Pada potongan script di atas, jika bValid = True, maka proses penyalinan akan didasari oleh jumlah baris (lRows). Idenya, proses penyalinan data hanya akan dilakukan jika ada data yang akan disalin.Namun hal tersebut disesuaikan saja dengan keinginan mas @Alan13...

    [3]. Untuk proses menyalin data secara berulang, buat dahulu sheet baru pada target Workbook, misalkan saja:

    ...
    Set xlTargetSheet = ThisWorkbook.Sheets.Add
    xlTargetSheet.Name = sh.Name
    ...

    [4]. Lakukan proses penyalinan data secara berulang dari sheet sumber ke sheet target:

    ...
    lRow = 1
    bDone = (lRows <= 100)
    lCount = IIf(bDone, lRows, 100)
    
    Do
        sh.Cells(lRow, 1).Resize(lRow + lCount, lCols).Copy
        xlTargetSheet.Range("A1").Offset(lRow).PasteSpecial xlPasteValues
        If lRows > 100 Then
            lRow = lRow + 100
            If (lRow \ 100) = (lRows \ 100) Then
                lCount = lRows - ((lRows \ 100) * 100)
            Else
                bDone = (lRow > lRows)
            End If
        End If
        DoEvents
    Loop While Not bDone
    ...

    [5]. Tutup Workbook sumber.

    Hal yang perlu diperhatikan antara lain adalah bentuk (layout) dari Workbook sumber, apakah data dimulai dari baris pertama atau tidak, untuk menyesuaikan variabel-variabel yang digunakan, seperti lRow.

    Demikian pengalaman dan pengetahuan yang dapat saya bagikan... ;)

 

atau Mendaftar untuk ikut berdiskusi!