Belajar Microsoft Excel: Menghasilkan Daftar Isi Otomatis

Halo sobat , jumpa lagi dengan je-xcel. Setelah sekian usang vakum menulis alasannya yakni kegiatan kerja. Alhamdulillah , kali ini saya sanggup kembali sedikit berbagi  kiat excel.

Dalam peluang ini kita akan membahas bagaimana menghasilkan daftar isi atau index worksheet secara otomatis.

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

Membahas perihal otomatisasi di excel , maka pastinya tidak sanggup lepas dari yang namanya VBA. Nah , dalam hal ini kita akan gunakan instruksi VBA untuk meng-generate list index dan hyperlinknya.
Anggaplah kita memiliki suatu file excel yang terdiri atas beberapa worksheet berisi data. Kemudian ada suatu worksheet berisi index data atau daftar isi. Atau lebih jelasnya sanggup dilihat dalam screenshot di bawah ini.
Data excel index otomatis
Tugas berikutnya yakni bagaimana meng-generate daftar isi worksheet index dan menghasilkan hyperlink ke data terkait , serta menghasilkan hyperlink balik dari worksheet data menuju sheet index.
Adapun caranya sungguh mudah , cukup ikuti langkah sederhana berikut ini:
  1. Pastikan tab developer pada aplikasi excel anda sudah aktif (excel 2007 atau yang lebih baru)  dan tetapkan setting macro security sudah enable.
  2. Klik kanan pada tab sheet index , lalu klik view code.
    cara masuk ke jendela VBA worksheet
  3. Selanjutnya kita akan dibawa ke jendela VBA menyerupai 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 , berikutnya kembali ke spreadsheet excel.
  6. Sampai dengan tahap ini , code VBA sudah sanggup dipakai untuk meng-generate list index , menghasilkan hyperlink ke sheet sasaran serta menghasilkan link back dari sheet data ke sheet index.
Untuk mengambarkan bahwa code VBA sanggup melakukan pekerjaan dengan baik , silahkan dicoba cara kerjanya dan lihat kesudahannya dengan cara berpindah ke sheet lain selain sheet index , lalu 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 pemberitahuan , sudah dilengkapi hyperlink yang mengarah pada worksheet terkait.

Jika perlu menyertakan sheet gres , atau mengganti nama sheet data , maka tidak perlu report untuk mengedit list index , alasannya yakni code VBA akan menyelesaikan kiprah ini secara otomatis setiap kali kita mengaktifkan sheet index.

Silahkan dicoba kembali dengan cara menyertakan sheet gres misalnya nama sheetnya “data baru”. Setelah itu , lalu kembali masuk ke sheet index. Maka kita akan mendapati sheet gres 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 kegunaannya sehingga user tidak perlu kelelahan menghasilkan hyperlink secara manual setiap kali ada pergantian pada nama sheet ataupun penambahan sheet baru.

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

Catatan: jikalau code yang dicontohkan dalam bimbingan ini tidak melakukan pekerjaan sesuai impian , maka kemungkinan setting macro security pada aplikasi microsoft excel yang anda pakai belum pas , sehingga macro tidak diizinkan untuk dijalankan. Silahkan periksa kembali setting macro security nya.

Demikian kiat singkat perihal bagaimana menghasilkan list index atau daftar isi secara otomatis menggunakan VBA pada microsoft excel. Semoga bermanfaat.
Baca Juga  Belajar Microsoft Excel: Memperlihatkan Nama Hari Dan Bulan Di Excel Dalam Bahasa Indonesia
Need Help? Chat with us