Salin kolom dari beberapa lembar menjadi satu vba

@ tridi94

Sub Macro1()

Dim i As Integer
Dim j As Integer
Dim z As Integer

z = 4
j = 3
For i = 2 To Worksheets.Count

Worksheets("main").Cells(j, 3).Value = Worksheets(i).Cells(1, 1).Value
Worksheets("main").Cells(z, 4).Value = Worksheets(i).Cells(3, 3).Value
Worksheets("main").Cells(z + 1, 4).Value = Worksheets(i).Cells(4, 3).Value
Worksheets("main").Cells(z, 5).Value = Worksheets(i).Cells(3, 4).Value
Worksheets("main").Cells(z + 1, 5).Value = Worksheets(i).Cells(4, 4).Value

j = j + 4
z = z + 4


Next i
End Sub

Mungkin dengan baris kode ini

VBA untuk menggabungkan data di beberapa Lembar Kerja ke Lembar Kerja yang baru dibuat di buku kerja yang sama. Kisaran di semua lembar kerja digabungkan ke dalam Lembar Kerja terkonsolidasi (Lembar Kerja akhir) satu demi satu secara berurutan. Jika data tidak tersedia di Lembar Kerja Sumber (i. e Lembar Kerja Input), data tidak akan diperbarui dalam Lembar Kerja gabungan. Berikut adalah penjelasan rinci langkah demi langkah untuk mengotomatisasi proses ini menggunakan VBA

Bagaimana kita akan mengembangkan modul proyek ini (Langkah-langkah KUNCI)

Untuk mengkonsolidasikan semua lembar kerja di buku kerja, pertama-tama kita harus membuat lembar kerja baru (mari kita panggil lembar master) dan kemudian mengulang setiap lembar kerja di buku kerja. Kami harus menemukan rentang data yang valid di setiap lembar kerja dan menambahkan lembar master yang baru dibuat di akhir baris

Izinkan saya menjelaskan langkah-langkah kunci untuk mengembangkan proyek ini. Kami akan menulis prosedur (Consolidate_Data_From_Different_Sheets_Into_Single_Sheet) dengan pendekatan di bawah ini

• Langkah 1. Deklarasi. Kami akan mendeklarasikan variabel dan objek yang diperlukan yang digunakan dalam prosedur kami
• Langkah 2. Nonaktifkan Pembaruan layar dan Acara. sementara untuk menghindari kedipan layar dan pemicu peristiwa
• Langkah 3. Hapus lembar Master lama. Sebelum membuat master sheet baru, kita harus memeriksa apakah ada sheet dengan nama yang sama dan menghapusnya
• Langkah 4. Menambahkan lembar kerja baru. Mari tambahkan lembar Master baru untuk menempelkan data dari lembar lain
• Langkah 5. Ulangi setiap lembar. Sekarang, mari kita ulangi setiap lembar kerja (sebut saja lembar sumber) dan tempel di lembar master
• Langkah 5. 1. Temukan Baris Terakhir yang Tersedia. Sekarang kita harus menemukan baris terakhir yang tersedia di lembar master untuk menempelkan data
• Langkah 5. 2. Temukan Baris yang terakhir digunakan dan kolom yang terakhir digunakan. Sekarang kita harus menemukan baris dan kolom terakhir dari lembar sumber
• Langkah 5. 3. Periksa apakah ada cukup data. Informasi yang didapat dari langkah di atas akan membantu untuk memeriksa apakah data tersedia di lembar sumber
• Langkah 5. 4. Salin data jika ada. Sekarang, salin data dari lembar sumber dan tambahkan ke lembar master
• Langkah 6. Aktifkan Pembaruan layar dan Acara. Mari setel ulang pembaruan layar dan acara

Catatan. Kami akan membuat dua fungsi yang ditentukan pengguna yang akan kami gunakan pada langkah 5 untuk menemukan baris terakhir dan kolom terakhir

Sekarang, mari kita lihat kode untuk setiap langkah

Langkah 1. Mendeklarasikan variabel yang digunakan di seluruh proyek

Dim Sht As Worksheet, DstSht As Worksheet
Dim LstRow As Long, LstCol As Long, DstRow As Long
Dim i As Integer, EnRange As String
Dim SrcRng As Range

Langkah 2. Nonaktifkan Pembaruan Layar digunakan untuk menghentikan kedipan layar dan Nonaktifkan Acara digunakan untuk menghindari kotak dialog/popup yang terputus

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

Langkah 3. Menghapus Lembar Kerja 'Consolidate_Data' jika ada di Buku Kerja. Dan Peringatan Tampilan digunakan untuk menghentikan sembulan saat menghapus Lembar Kerja

Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Sheets("Consolidate_Data").Delete
Application.DisplayAlerts = True

Langkah 4. Menambahkan Lembar Kerja baru di akhir Lembar Kerja. Penamaan sebagai 'Consolidate_Data'. Dan akhirnya ditugaskan ke objek (DstSht)

With ActiveWorkbook
    Set DstSht = .Sheets.Add(After:=.Sheets(.Sheets.Count))
    DstSht.Name = "Consolidate_Data"
End With

Langkah 5. Itu adalah lLooping melalui setiap (atau semua) Lembar Kerja di buku kerja

Dan jika pernyataan memeriksa lembar Input (Input Data) dan lembar tujuan (Lembar Konsolidasi) sama atau tidak. Jika sama maka akan memeriksa lembar kerja berikutnya. Jika tidak sama dengan maka itu menyalin data input dan menggabungkannya ke Lembar Kerja Konsolidasi

For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> DstSht.Name Then
End if

Langkah 5. 1. Menemukan baris terakhir di Lembar Kerja 'Consolidate_Data' menggunakan fungsi 'fn_LastRow'

DstRow = fn_LastRow(DstSht) + 1

Langkah 5. 2. Menemukan baris yang terakhir digunakan dan kolom yang terakhir digunakan di Lembar Kerja Input dan menugaskannya ke objek LstRow dan LstRow

Menemukan alamat sel yang terakhir digunakan di Lembar Kerja dan menugaskannya ke objek EnRange. Akhirnya menemukan rentang data Input di Lembar Kerja Input dan menugaskannya ke objek 'SrcRng'

LstRow = fn_LastRow(Sht)
       LstRow = fn_LastColumn(Sht)
       EnRange = Sht.Cells(LstRow, LstCol).Address
       Set SrcRng = Sht.Range("A1:" & EnRange)

Langkah 5. 3. Periksa apakah ada cukup baris di Lembar Kerja 'Consolidate_Data'. Kalau tidak, itu menampilkan pesan ke pengguna dan pergi ke IfError

If DstRow + SrcRng.Rows.Count > DstSht.Rows.Count Then
            MsgBox "There are not enough rows to place the data in the Consolidate_Data worksheet."
            GoTo IfError
        End If

Langkah 5. 4. Menyalin data dari Lembar Kerja masukan dan menggabungkannya dengan Lembar Kerja tujuan

SrcRng.Copy Destination:=DstSht.Range("A" & DstRow)

Langkah 6. Mengaktifkan Pembaruan Layar dan Acara di akhir proyek

With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

Kode Modul VBA Akhir (Makro)

Temukan makro berikut untuk Mengkonsolidasikan data dari Lembar Kerja yang berbeda dari Buku Kerja

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

Di bawah ini adalah dua fungsi yang ditentukan pengguna yang telah kami buat untuk menemukan baris terakhir dan kolom terakhir dari lembar kerja yang diberikan. Kami telah memanggil fungsi-fungsi ini dalam prosedur di atas pada langkah 5. 1 dan 5. 2

Berfungsi untuk Menemukan Baris Terakhir

Fungsi berikut akan menemukan baris terakhir dari lembar kerja yang diberikan. 'fn_LastRow' akan menerima lembar kerja (Sht) sebagai input dan memberikan baris terakhir sebagai output

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

Berfungsi untuk Menemukan Kolom Terakhir

Fungsi berikut akan menemukan kolom terakhir dari lembar kerja yang diberikan. 'fn_LastColumn' akan menerima lembar kerja (Sht) sebagai input dan memberikan kolom terakhir sebagai output

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

Modul Akhir
Ayo

Instruksi untuk Mengeksekusi Prosedur

Anda dapat mengunduh file di bawah ini dan melihat kode dan menjalankannya. Atau yang lain, Anda membuat buku kerja baru dan menggunakan kode di atas dan mengujinya. Berikut adalah petunjuk untuk menggunakan kode di atas

1. Buka jendela Editor VBA atau Tekan Alt+F11
2. Sisipkan modul baru dari menu Sisipkan
3. Salin prosedur dan fungsi di atas dan tempel di modul yang baru dibuat
4. Anda dapat memasukkan beberapa data sampel dalam beberapa lembar. dan menjalankan prosedur

Unduh Buku Kerja Proyek – File Makro Excel

Berikut adalah contoh Buku Kerja makro Excel untuk dijelajahi sendiri

Unduh. Gabungkan data dari Lembar Kerja yang berbeda menjadi satu Lembar Kerja Menurut Baris

Kelola Proyek dan Sumber Daya Anda dengan Mudah

120+ Template Manajemen Proyek Profesional

Template yang Kuat & Serba Guna untuk manajemen proyek. Sekarang kelola proyek, tugas, rapat, presentasi, tim, pelanggan, pemangku kepentingan, dan waktu Anda dengan lancar. Halaman ini menjelaskan semua fitur dan opsi baru yang luar biasa yang disertakan dengan template premium kami

Hemat Hingga 85% PENAWARAN WAKTU TERBATAS

Telusuri Semua Template

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

Paket All-in-One
120+ Template Manajemen Proyek

Melihat rincian

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

Paket Esensial
50+ Template Manajemen Proyek

Melihat rincian

Paket Excel
50+ Templat PM Excel

Melihat rincian

Paket PowerPoint
50+ Templat PM Excel

Melihat rincian

Paket MS Word
25+ Templat Word PM

Melihat rincian

Templat Manajemen Proyek Utama

Melihat rincian

Templat Manajemen Sumber Daya Utama

Melihat rincian

Template Manajemen Portofolio Proyek

Melihat rincian

Bagikan Kisah Ini, Pilih Platform Anda

11 Komentar

1. akram 17 Januari 2017 pukul 11. 10 pagi

   Apakah ada cara untuk menghapus garis dari A1 ”DstSht. Range(“A1”) = “Anda dapat menempatkan judul di baris pertama” dan menempelkan data dari sel A1 sebagai gantinya

2. akram 17 Januari 2017 pukul 11. 13 pagi

   Juga dapatkah kita menentukan dari sheet mana kita membutuhkan data untuk dikonsolidasikan alih-alih mempertimbangkan semua sheet

3. Sumut 1 Februari 2017 pukul 8. 31 sore

   Kode bekerja dengan sempurna. Inilah yang saya butuhkan. Terima kasih banyak

4. Kathleen 27 Juni 2017 pukul 10. 51 sore

   Bisakah Anda menyarankan bagaimana Anda dapat mengatur rentang lembar untuk menarik data dari vs semua?

5. Lindsay Tweed 12 Desember 2017 pukul 12. 41 pagi

   Ini muncul dengan kesalahan "sub atau Fungsi tidak ditentukan 'untuk langkah 5. 1 “DstRow = fn_LastRow(DstSht) + 1” pada saya, ada pemikiran tentang cara memperbaikinya?

6. ozz 22 Desember 2017 pukul 3. 28 malam

   Halo semuanya,

   Pertama-tama saya harus memberi tahu bahwa saya tidak memiliki pengalaman dengan Makro (Kode VBA). Namun yang saya butuhkan terkait dengan ini. Mungkin kalian bisa membantu saya dengan itu

   Saya memiliki buku kerja dan dalam buku kerja ini ada 10 lembar kerja. 9 Lembar pertama memiliki urutan kolom judul yang sama dan di kolom ini ada nama, tanggal, persentase Status Proyek, komentar untuk Proyek dll. Seperti yang saya katakan kolom memiliki urutan yang sama hanya nama lembar kerja (untuk Tim yang berbeda di Organisasi) berbeda

   Selain itu saya harus menggabungkan semua lembar kerja dan memilikinya di lembar lain yang disebut "Übersicht" (Tinjauan). Namun ada kolom yang berbeda di lembar dan itu antara “Nr. ” dan kolom “Thema” (yang ada di A1 dan A2 di semua 9 Lembar) dan kolom berbeda ini disebut “Kategorie” (dalam A2 di lembar Übersicht-Overwiev). Karena kolom ini berada di antara Ini, urutannya seperti ini “Nr. (A1), Kategorie (A2) dan Tema (A3)…. ”. Jadi kolom kategori ini (Kategorie) harus kosong kecuali ini semua Informasi harus digabungkan ke dalam lembar ini. Dan juga ketika ada perubahan atau pembaharuan di lembar kerja manapun, Informasi di lembar “Übersicht” (Ikhtisar) perlu dimutakhirkan dengan sendirinya. Bagaimana saya bisa melakukan ini?

   P. S. Setiap lembar memiliki baris isi yang berbeda, ada yang 30, ada yang 13, ada yang 5 dll. Dan Tim yang bertanggung jawab atas Lembar dapat menambah atau menghapus beberapa baris (di setiap baris ada Informasi berbeda untuk Proyek berbeda). Ini juga berarti jumlah baris dapat bertambah atau berkurang

   Saya harap saya menjelaskannya dengan baik. Terima kasih banyak sebelumnya

   Saya mengucapkan selamat Natal dan tahun baru

   oduff

7. Kristal 3 Agustus 2018 pukul 9. 59 sore

   Kode ini berfungsi luar biasa untuk apa yang ingin saya lakukan. Namun, saya punya 2 pertanyaan. Bagaimana saya bisa mendapatkan langkah sebelum "mengaktifkan pembaruan layar" ("Anda dapat menempatkan judul di baris pertama"), untuk benar-benar menyalin salah satu baris pertama di lembar lainnya. Semua lembar saya memiliki kolom tajuk yang sama. Jadi, saya ingin lembar gabungan juga memiliki tajuk itu

   Pertanyaan kedua adalah bagaimana menempatkan lembar gabungan sebagai tab pertama di lembar kerja, bukan yang terakhir

   Dan terakhir, saya ingin menyalin format dari lembar sebelumnya (menggunakan Format Painter di seluruh lembar) ke Lembar Gabungan

   Terima kasih banyak

8. Arash 10 Juni 2019 pukul 2. 28 pagi

   Halo,

   ini berfungsi dengan baik tetapi terkadang saya memiliki tajuk yang berbeda atau tajuk berada di posisi yang berbeda di lembar yang berbeda
   Bagaimana mungkin
   – memiliki semua tajuk di posisi yang sama saat membuat lembar master
   – dan tambahkan header ekstra di akhir lembar master jika tidak sama
   – bagikan buku kerja xml untuk lebih dari satu pihak untuk masuk secara bersamaan
   – Saya memiliki tajuk di setiap lembar tetapi lembar master harus merupakan ringkasan dari semua tajuk agar tidak menduplikasi tajuk sepanjang waktu di lembar master
   –

9. pradeep. hatti 11 Juli 2019 pukul 6. 33 sore

   Tolong bantu untuk memperbaiki di bawah ini, karena saya mencoba menyalin data dari buku master buku kerja multi, tetapi untuk menyalin hanya tanggal di sel 11- Teks =

   Sub DPR_Test1()

   'makro ini masuk ke dalam buku kerja utama
   Dim wsMaster Sebagai Lembar Kerja, Lembar1 Sebagai Buku Kerja
   Redupkan Baris Berikutnya Selama, baris terakhir Selama
   Dim FileName Sebagai String
   Redupkan FolderPath Sebagai String
   Redup dan Panjang
   redup saya

   Atur wsMaster = Buku Kerja Ini. Lembar ("Lembar1")

   'Tentukan jalur folder

   FolderPath = “C. UserhattipDesktopDPR”

   'menentukan nama file

   FileName = Dir(FolderPath & “*. xls*”)

   Lakukan Saat FileName ”

   NextRow = wsMaster. Rentang ("A" & Baris. Menghitung). Akhir(xlUp). Baris + 1

   Jika wbDATA(“sheet1”). Sel (i, 11). Nilai = “Meksiko” Lalu

   Atur wbDATA = Buku Kerja. Buka (FolderPath & FileName)

   Dengan wbDATA. Lembar ("Lembar1")
   baris terakhir =. Rentang ("A" &. Baris. Menghitung). Akhir(xlUp). Baris
   ‘ Jika LastRow > 5 Kemudian
   Untuk i = 2 Ke baris terakhir

   Rentang ("A2. ah aku). Salinan
   wsMaster. Rentang ("A" & Baris Berikutnya). TempelSpesial xlPasteValues
   'Atur NextRow = NextRow
   Selanjutnya saya
   Berakhir dengan
   Nama Berkas = Dir()

   Berakhir jika
   Aplikasi. Pembaruan Layar = Benar

   Lingkaran

   Akhir Sub

10. mahboob 27 Agustus 2019 pukul 2. 07 malam

    Yang terhormat,

    Saya ingin belajar makro VBA dengan Anda

    Terima kasih banyak untuk file MIS konsulat

    Tolong bagikan kontrak Anda no

    Surai saya adalah mahboob alam

    Saya bekerja di Mumbai penunjukan saya adalah MIS. WFM dan ingin mempelajari makro VBA

11. VinnieB 15 Januari 2020 pukul 1. 08 pagi

    Kode berfungsi dengan sangat baik. Apakah ada cara untuk menambahkan kode, sehingga kolom "A" berisi nama tab untuk data asalnya?

    Bagaimana cara menyalin data dari beberapa lembar ke satu lembar dalam kode Excel VBA?

    Cara menduplikasi sheet di Excel dengan VBA .

    Buka lembar kerja yang ingin Anda salin

    Tekan Alt + F11 untuk membuka Editor Visual Basic

    Di panel kiri, klik kanan Buku Kerja Ini, lalu klik Sisipkan > Modul

    Tempel kode di jendela Kode

    Tekan F5 untuk menjalankan makro

    Bagaimana cara menggabungkan data dari beberapa lembar menjadi satu menggunakan VBA?

    Buka file Excel tempat Anda ingin menggabungkan lembar dari buku kerja lain dan lakukan hal berikut. .

    Tekan Alt + F8 untuk membuka dialog Makro

    Di bawah nama Makro, pilih MergeExcelFiles dan klik Jalankan

    Jendela penjelajah standar akan terbuka, Anda memilih satu atau lebih buku kerja yang ingin Anda gabungkan, dan klik Buka

    Bagaimana cara menyalin kolom dari beberapa lembar?

    Versi cepat. Pilih data untuk disalin > Tahan Ctrl atau Shift dan pilih lembar kerja untuk disalin > tab Beranda > Tombol Isi (sebelah kanan menu pita) > Di Seluruh Lembar Kerja > Oke.

    Bagaimana cara menyalin data dari beberapa lembar ke satu lembar?

    Gabungkan beberapa lembar kerja menjadi satu dengan Lembar Salinan .

    Mulai Panduan Salin Lembar. Pada pita Excel, buka tab Ablebits, grup Gabung, klik Salin Lembar, dan pilih salah satu opsi berikut. .

    Pilih lembar kerja dan, secara opsional, rentang untuk digabungkan. .

    Pilih cara menggabungkan sheet