Halo, Pembaca Klinik Excel.. Bantu kami tetap online yuk..
Cloning Beberapa File Sekaligus (dengan mengubah formula jadi value)
- Category
- Lain-lain
- Posted
-
By:
ngarasan, On: Saturday, November 05, 2011, Last Modified: Friday, November 13, 2020 - Info
- Hits: 9708, Comments: 7, Bookmarked: 0, Followers: 7
Halo semua..
Kali ini saya akan membuat sebuah Project Macro (disertai file contoh tentu saja) yang merupakan jawaban untuk pertanyaan dari Mas Erpin pada link berikut:
http://www.klinikexcel.com/forum-a-request/user-article/2-excel/168-piens/89-2-pertanyaan-pa-guru-.html
Pertanyaanya:
Bagaimana meng-copy semua sheet pada sebuah Workbook ke workbook yang baru namun hanya value-nya saja. Formula diubah dulu menjadi value.
Dan menurut saya, dari pada mengcopy semua sheet dari workbook A ke workbook B, lebih baik meng-copy saja Workbooknya, baru kemudian isinya kita ubah menjadi value. Dengan demikian, akan lebih cepat dan bisa sama persis!
Lalu gimana caranya?
Persiapan:
1. Saya buat sebuah workbook yang saya beri nama Clone_Macro.xlsm.
2. Saya buat sebuah worksheet bernama "Utama". Anda bisa mengubahnya sesuai nama yang anda suka.
3. Saya letakkan sebuah Control Form Command Button di sheet tersebut di atas dan memberinya caption : "CLONE!" (sekali lagi, ini suka-suka anda saja).
4. Kemudian saya tulis code pada Module dengan nama Sub Procedure: "Clone"
5. Selanjutnya, saya tulis code berikut:
Oya, penjelasan untuk tiap-tiap baris code telah tersedia pada "remark"-nya yah.
Sub clone()
Dim JumlahFile As Integer
Dim NamaFileUtama, NamaFileBaru, NamaFileTerbuka, PathFileTerbuka
Dim sh As Worksheet
'code untuk memilih file.
'code ini akan memunculkan window dialog untuk memilih file.
'file yang dipilih akan disimpan di variabel "FileTerpilih".
FileTerpilih = Application.GetOpenFilename("Excel File (*.xlsx),*.xlsx,XLS Files (*.xls),*.xls", _
Title:="Pilih file..", MultiSelect:=True)
'jika pengguna membatalkan pemilihan file, maka keluar dari Sub prosedur.
If VarType(FileTerpilih) = vbBoolean Then
Exit Sub
End If
'simpan dulu nama file utama atau file yang memiliki macro untuk proses pemanggilan nanti.
NamaFileUtama = ActiveWorkbook.Name
'cari jumlah file yang dipilih oleh pengguna.
JumlahFile = UBound(FileTerpilih)
'mulai buka file satu per satu dengan looping.
For i = 1 To JumlahFile
'matikan diplay alert agar tidak muncul window yang membutuhkan interaksi user.
Application.DisplayAlerts = False
'Buka file yang dipilih.
Workbooks.Open FileTerpilih(i)
'simpan nama file yang sedang dibuka.
NamaFileTerbuka = ActiveWorkbook.Name
PathFileTerbuka = ActiveWorkbook.Path
'tampilkan informasi progres di status bar
Application.StatusBar = "Working.. " & NamaFileTerbuka & " (" & i & " / " & JumlahFile & ")"
'pastikan, apakah extension file xlsx atau xls.
If Right(NamaFileTerbuka, 4) = "xlsx" Then
'Copy atau save as file yang terbuka atau proses nge-CLONE (versi xlsx).
'Nama file akan disimpan dengan nama file lama _ "_copy".
ActiveWorkbook.SaveAs Filename:=PathFileTerbuka & "\" & _
Left(NamaFileTerbuka, Len(NamaFileTerbuka) - 5) & "_copy.xlsx", _
FileFormat:=51, CreateBackup:=False
Else
'Copy atau save as file yang terbuka atau proses nge-CLONE (versi xls).
'Nama file akan disimpan dengan nama file lama _ "_copy".
ActiveWorkbook.SaveAs Filename:=PathFileTerbuka & "\" & _
Left(NamaFileTerbuka, Len(NamaFileTerbuka) - 4) & "_copy.xls", _
FileFormat:=56, CreateBackup:=False
End If
'proses copy paste value untuk membuang formula.
'lakukan looping untuk semua sheet yang ada dalam workbook.
For Each sh In Worksheets
'ubah semua formula menjadi value
sh.Range(sh.UsedRange.Address).Value = _
sh.Range(sh.UsedRange.Address).Value
Next
'simpan file
ActiveWorkbook.Save
'tutup file
ActiveWorkbook.Close
'tampilkan status done pada status bar
Application.StatusBar = "Done.. "
'aktifkan diplay alert agar tidak muncul window yang membutuhkan interaksi user.
Application.DisplayAlerts = True
Next i
MsgBox "Proses Cloning selesai!"
End Sub
5. SELESAI!!!
Cepat banget kan bikinnya..
Nah sekarang,
Gimana cara menggunakannya?
1. Klik aja tombol "Clone!" yang akan memanggil macro clone yang tadi kita buat.
2. Akan muncul window untuk memilih file. Pilih file Excel yang ingin di Clone. Kita bisa memilih beberapa file sekaligus.
3. Klik open dan macro akan bekerja.
4. Macro akan membuat copy dari file yang kita pilih dan menambahkan "_copy" pada nama file nya. Misal, nama file "contoh.xlsx" akan di-clone menjadi "contoh_copy.xlsx".
5. Selain itu, jika ada sheet yang memiliki formula didalamnya, maka akan langsung di ubah menjadi value. Ini permintaan khusus mas Erpin. tentu saja anda bisa menghilangkannya.
6. Nah, berikut adalah contoh file sumber dan file hasilnya..
Oke.. begitu saja kok.. Jika dibutuhkan, silahkan download file contohnya..
password: puyengbanget
Semoga bermanfaat.. Sampai ketemu lagi...
salam
ula yang serius pengen belajar MACRO VBA Excel, silahkan baca buku berikut: