Cara membuat daftar otomatis di Excel

Halo teman, jumpa lagi dengan je-xcel. Setelah sekian lama vakum menulis karena kesibukan kerja. Alhamdulillah, kali ini saya bisa kembali sedikit berbagi  tips excel.

Dalam kesempatan ini kita akan membahas bagaimana membuat daftar isi atau index worksheet secara otomatis.

Dengan semakin banyaknya lembar kerja atau worksheet pada sebuah file excel, maka mungkin kita akan merasa kesulitan untuk navigasi antar sheet. Nah, untuk itu diperlukan sebuah alat bantu sebuah worksheet berisi list index beserta hyperlinknya yang dapat di-generate secara otomatis.

Membahas mengenai otomatisasi di excel, maka tentunya tidak bisa lepas dari yang namanya VBA. Nah, dalam hal ini kita akan gunakan kode VBA untuk meng-generate list index dan hyperlinknya.

Anggaplah kita memiliki sebuah file excel yang terdiri atas beberapa worksheet berisi data. Kemudian ada sebuah worksheet berisi index data atau daftar isi. Atau lebih jelasnya dapat dilihat dalam screenshot di bawah ini.

Tugas selanjutnya adalah bagaimana meng-generate daftar isi worksheet index dan membuat hyperlink ke data terkait, serta membuat hyperlink balik dari worksheet data menuju sheet index.

Adapun caranya sangat mudah, cukup ikuti langkah sederhana berikut ini:

1. Pastikan tab developer pada aplikasi excel anda sudah aktif (excel 2007 atau yang lebih baru)  dan pastikan setting macro security sudah enable.
2. Klik kanan pada tab sheet index, kemudian klik view code.
   

3. Selanjutnya kita akan dibawa ke jendela VBA seperti terlihat pada gambar berikut:
   

4. Copy code berikut ke dalam private modul sheet1(index)
   
   Private Sub Worksheet_Activate()
   Dim ws As Worksheet, index As Integer
   Application.ScreenUpdating = False
   Me.Cells.Clear
   Me.Cells(1, 1).Name = "Index"
   Me.Cells(1, 1).Value = "Index"
   Me.Cells(1, 2).Value = "Keterangan"
   For Each ws In ThisWorkbook.Worksheets
     If ws.Name <> Me.Name Then
       index = index + 1
       Me.Cells(index + 1, 1).Value = index
       ws.Cells(1, 1).Name = "index" & index
       ws.Cells(1, 1).Value = "<< index"
       Me.Hyperlinks.Add Me.Cells(index + 1, 2), "", "index" & index, "Lihat Data", ws.Name
       ws.Hyperlinks.Add ws.Cells(1, 1), "", "index", "Lihat index", "<< Index"
     End If
   Next
   Application.ScreenUpdating = True
   End Sub
   
   
   
5. Kemudian close jendela VBA, selanjutnya kembali ke spreadsheet excel.

6. Sampai dengan tahap ini, code VBA sudah dapat digunakan untuk meng-generate list index, membuat hyperlink ke sheet target serta membuat link back dari sheet data ke sheet index.

Untuk membuktikan bahwa code VBA dapat bekerja dengan baik, silahkan dicoba cara kerjanya dan lihat hasilnya dengan cara berpindah ke sheet lain selain sheet index, kemudian kembali ke sheet index. Maka secara otomatis pada sheet index akan di-generate daftar isi berupa nomor index dilengkapi keterangannya sesuai nama-nama sheet yang ada dalam workbook excel. Selain itu pada masing-masing keterangan, sudah dilengkapi hyperlink yang mengarah pada worksheet terkait.

Jika perlu menambahkan sheet baru, atau merubah nama sheet data, maka tidak perlu report untuk mengedit list index, karena code VBA akan menyelesaikan tugas ini secara otomatis setiap kali kita mengaktifkan sheet index.

Silahkan dicoba kembali dengan cara menambahkan sheet baru misalnya nama sheetnya “data baru”. Setelah itu, kemudian kembali masuk ke sheet index. Maka kita akan mendapati sheet baru secara  otomatis terdaftar dalam index.

Selain itu pada sel A1 dari setiap sheet data akan tercipta secara otomatis hyperlink yang mengarah ke sheet index.

List index, hyperlink ke sheet data, serta hyperlink balik ke sheet index akan disegarkan secara otomatis setiap kali user masuk atau mengaktifkan sheet index. Disinilah letak keuntungannya sehingga user tidak perlu capek membuat hyperlink secara manual setiap kali ada perubahan pada nama sheet ataupun penambahan sheet baru.

Setelah selesai, maka file excel latihan ini dapat di simpan. Jika menggunakan excel 2007 atau yang lebih baru, jangan lupa untuk save as sebagai  excel macro – enable workbook atau excel binary workbook. Jika tidak, maka code macro akan terhapus dan tidak dapat digunakan.

Catatan: jika code yang dicontohkan dalam tutorial ini tidak bekerja sesuai harapan, maka kemungkinan setting macro security pada aplikasi microsoft excel yang anda gunakan belum pas, sehingga macro tidak diizinkan untuk dijalankan. Silahkan periksa kembali setting macro security nya.

Demikian tips singkat mengenai bagaimana membuat list index atau daftar isi secara otomatis menggunakan VBA pada microsoft excel. Semoga bermanfaat.

Bagaimana cara membuat data secara otomatis di excel?

Langkah membuat penomoran otomatis pada Excel?

Bagaimana cara menggunakan auto fill?

Bagaimana cara memasukkan data yang sama ke banyak sel?