Misalkan, Anda memiliki buku kerja dengan beberapa lembar kerja, sekarang Anda ingin mencantumkan semua nama lembar di buku kerja saat ini, apakah ada metode cepat untuk membuat daftar nama lembar ini di Excel tanpa mengetiknya satu per satu? Tutorial ini memberikan instruksi tentang cara membuat daftar nama lembar kerja di Excel.

Dapatkan daftar semua nama lembar kerja dari buku kerja secara dinamis dengan rumus

Dapatkan daftar semua nama lembar kerja dari buku kerja dengan fitur yang berguna

Dapatkan daftar semua nama lembar kerja dari buku kerja dengan kode VBA

Dapatkan daftar semua nama lembar kerja dari buku kerja secara dinamis dengan rumus

Di Excel, Anda dapat menentukan nama rentang, lalu menerapkan rumus untuk mencantumkan semua nama lembar dari buku kerja saat ini, lakukan dengan langkah-langkah berikut:

1. Pergi ke klik Rumus > Manajer nama, lihat tangkapan layar:

2. di Manajer nama kotak dialog, klik New tombol, lihat tangkapan layar:

3. Di muncul keluar Nama baru dialog, tentukan nama ke dalam Nama kotak teks, lalu salin rumus di bawah ini ke dalam Mengacu pada kotak teks, lihat tangkapan layar:

=GET.WORKBOOK(1)&T(NOW())

4. Lalu klik OK > Penyelesaian untuk menutup kotak dialog, sekarang, silakan masuk ke lembar di mana Anda ingin mencantumkan semua nama lembar, lalu masukkan rumus berikut ke dalam sel kosong:

=IFERROR(INDEX(MID(Sheetnames,FIND("]",Sheetnames)+1,255),ROWS($A$2:A2)),"")

Note: Dalam rumus di atas, Nama lembar adalah nama rentang yang Anda capai di langkah 3.

5. Dan kemudian seret pegangan isian ke sel ketika sel kosong ditampilkan, dan sekarang, semua nama lembar dari buku kerja saat ini telah terdaftar seperti gambar di bawah ini yang ditunjukkan:

6. Jika Anda ingin membuat hyperlink untuk setiap lembar, gunakan rumus di bawah ini:

=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")

Note: Dalam rumus di atas, A2 adalah sel yang berisi nama sheet, dan A1 adalah sel tempat Anda ingin meletakkan sel aktif. Misalnya, jika Anda mengklik teks hyperlink, itu akan menemukan sel A1 dari lembar.

7. Sekarang, ketika Anda mengklik teks hyperlink, itu akan membawa Anda ke sheet itu, lihat demo di bawah ini:

Dapatkan daftar semua nama lembar kerja dari buku kerja dengan fitur yang berguna

Dengan Buat Daftar Nama Lembar utilitas add-in pihak ketiga Kutools untuk Excel, Anda dapat membuat daftar nama lembar kerja dalam satu klik, dan menautkan ke setiap lembar kerja dengan hyperlink.

Setelah menginstal Kutools untuk Excel, lakukan seperti ini:

1. Klik Kutools Ditambah > Lembar Kerja > Buat Daftar Nama Lembar, lihat tangkapan layar:

2. di Buat Daftar Nama Lembar kotak dialog, tentukan pengaturan berikut:

(1.) Pilih gaya indeks lembar Anda suka, Anda bisa membuat nama lembar kerja dengan hyperlink atau dengan tombol makro. (2.) Masukkan nama lembar kerja untuk indeks lembar. (3.) Tentukan lokasi indeks lembar kerja. (4.) Tentukan berapa banyak kolom yang ingin Anda gunakan di lembar kerja baru untuk menampilkan nama lembar kerja.

3. Setelah selesai melakukan setting, selanjutnya klik OK. Semua nama lembar kerja telah dicantumkan dengan tautan di lembar kerja baru dari buku kerja saat ini. Lihat tangkapan layar:

nama lembar kerja dengan hyperlink	nama lembar kerja dengan tombol makro

Dapatkan daftar semua nama lembar kerja dari buku kerja dengan kode VBA

1. Tahan ALT + F11 kunci, dan itu membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Klik Menyisipkan > Modul, dan tempelkan makro berikut di file Modul Jendela.

VBA: Cantumkan semua nama lembar kerja dengan hyperlink di lembar kerja baru:

Sub CreateIndex()
'updateby Extendoffice
    Dim xAlerts As Boolean
    Dim I  As Long
    Dim xShtIndex As Worksheet
    Dim xSht As Variant
    xAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Index").Delete
    On Error GoTo 0
    Set xShtIndex = Sheets.Add(Sheets(1))
    xShtIndex.Name = "Index"
    I = 1
    Cells(1, 1).Value = "INDEX"
    For Each xSht In ThisWorkbook.Sheets
        If xSht.Name <> "Index" Then
            I = I + 1
            xShtIndex.Hyperlinks.Add Cells(I, 1), "", "'" & xSht.Name & "'!A1", , xSht.Name
        End If
    Next
    Application.DisplayAlerts = xAlerts
End Sub

3. Tap F5 kunci untuk menjalankan makro ini. Sekarang semua nama lembar kerja di buku kerja aktif telah dicantumkan ke dalam lembar kerja baru bernama Indeks, dan nama lembar juga ditautkan ke setiap lembar, lihat tangkapan layar:

Terkait artikel