Featured Post

Lookup Gambar Dengan INDEX MATCH

Apakah Pencarian Gambar bisa dilakukan di excel? Pertanyaan ini sangat menarik sekali untuk dibahas. Jika pembaca mengikuti blog ini, pada ...

Sunday, September 2, 2018

Membuat Daftar Isi Otomatis

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.


Data excel index otomatis


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.

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

    Jendela VBA Excel Worksheet
  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


    Cara copy code pada vba excel



  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.

contoh daftar isi dan hyperlink otomatis

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

contoh hyperlink back




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.

3 comments:

  1. Matur suwun ilmunya pak, sangat membantu sekali

    ReplyDelete
  2. pak.....bagaimana agar index di masing2 sheet tidak menimpa data baris pertama yang ada isinya (ada judul). jadi harus insert baris pertama baru bisa di indexkan mas.

    ReplyDelete

Terimakasih sudah berkunjung dan membaca blog ini. Silahkan berkomentar.