Makro untuk daftar nama sheet di excel

Jika Anda memiliki beberapa lembar dalam buku kerja, dan Anda ingin membuat daftar dinamis semua nama lembar dalam lembar baru di buku kerja, bagaimana caranya?

Buat daftar nama lembar kerja yang dinamis dengan Tentukan Nama dan Rumus

1. Pilih sel di lembar kosong, di sini saya pilih A1, lalu klik Rumus > Tentukan Nama. lihat tangkapan layar

2. Kemudian dalam dialog Nama Baru, ketik Lembar ke dalam kotak teks Nama (Anda dapat mengubah sesuai kebutuhan), dan ketik rumus ini =SUBSTITUTE(GET. LEMBAR KERJA(1),"["&GET. WORKBOOK(16)&"]","") ke dalam kotak teks Merujuk ke. Lihat tangkapan layar.

3. Klik Oke. Pergi ke sel yang dipilih (A1) dan ketik rumus ini =INDEX(Sheets,ROWS($A$1. $A1)) (A1 adalah sel tempat Anda mengetik rumus ini, "Sheets" adalah nama yang Anda tentukan di Langkah 2) ke dalamnya, lalu seret gagang isi otomatis ke bawah hingga #REF. muncul.

Tip. Jika ada lembar kerja yang dihapus atau ditambahkan, Anda harus pergi ke A1 dan tekan tombol Enter lalu gores gagang pengisian otomatis lagi

Buat daftar nama lembar kerja yang dinamis dengan kode VBA

Jika Anda ingin membuat daftar nama lembar kerja yang dinamis yang dapat ditautkan ke setiap lembar, Anda dapat menggunakan kode VBA

1. Buat lembar kerja baru dan ganti namanya menjadi Index. Lihat tangkapan layar

2. Klik kanan pada nama sheet Index, pilih View Code dari menu konteks. Lihat tangkapan layar

3. Di jendela yang muncul, salin dan tempel kode VBA di bawah ini ke dalamnya

VBA. Buat daftar nama lembar kerja yang dinamis

Private Sub Worksheet_Activate()
	'Updateby20150305
	Dim xSheet As Worksheet
	Dim xRow As Integer
	Dim calcState As Long
	Dim scrUpdateState As Long
	Application.ScreenUpdating = False
	xRow                       = 1
	With Me
		.Columns(1).ClearContents
		.Cells(1, 1) = "INDEX"
		.Cells(1, 1).Name = "Index"
	End With
	For Each xSheet In Application.Worksheets
		If xSheet.Name <> Me.Name Then
			xRow                     = xRow + 1
			With xSheet
				.Range("A1").Name = "Start_" & xSheet.Index
				.Hyperlinks.Add anchor: = .Range("A1"), Address: = "", _
				SubAddress:             = "Index", TextToDisplay: = "Back to Index"
			End With
			Me.Hyperlinks.Add anchor: = Me.Cells(xRow, 1), Address: = "", _
			SubAddress: = "Start_" & xSheet.Index, TextToDisplay: = xSheet.Name
		End If
	Next
	Application.ScreenUpdating = True
End Sub

4. Klik Jalankan atau F5 untuk menjalankan VBA, sekarang daftar nama lembar kerja yang dinamis dibuat

Tip

1. Ketika lembar kerja buku kerja dihapus atau disisipkan, daftar nama lembar kerja akan berubah secara otomatis

2. Anda dapat mengklik nama sheet dari daftar nama untuk pergi ke sheet

Dengan dua metode di atas tidak cukup nyaman, jika Anda tertarik dengan utilitas baru, Anda dapat menggunakan dua metode berikutnya yang dapat membuat tugas ini lebih mudah

Buat daftar nama lembar kerja yang dinamis dengan Kutools for Excel

Jika Anda hanya ingin mencantumkan semua nama lembar kerja dari buku kerja dengan cepat dan menautkannya ke lembar asli, Anda dapat menggunakan Kutools for Excel's Buat Daftar Nama Lembar

Download Gratis
fitur lengkap gratis dalam 30 hari

Setelah menginstal gratis Kutools for Excel, silakan lakukan seperti di bawah ini.

1. Klik Kutools Plus > Worksheet > Buat Daftar Nama Sheet. Lihat tangkapan layar

2. Dalam dialog Buat Daftar Nama Lembar

(1) Periksa gaya indeks yang Anda butuhkan di bagian Gaya Indeks Lembar;
(2) Beri nama lembar indeks baru dengan mengetikkan data ke dalam kotak teks Specify sheet name for Sheet Index;
(3) Tentukan lokasi tempat Anda akan menempatkan lembar indeks tambahan di dalam daftar Sisipkan Indeks Lembar dalam;
(4) Jika Anda ingin menampilkan nama sheet dalam satu daftar, pilih 1 kolom di Displaying Sheet Index into list

3. Klik Oke. Sekarang Anda dapat melihat nama sheet terdaftar

Kiat

1. Anda dapat mengklik nama sheet untuk segera beralih ke sheet aslinya

2. Nama daftar atau sheet tidak dapat berubah secara dinamis saat sheet dimasukkan atau dihapus

3. Sebenarnya, Anda juga dapat membuat daftar tombol untuk menautkan lembar relatif, hanya perlu mencentang Berisi Tombol dan Makro di dialog. lihat tangkapan layar

Klik di sini untuk mengetahui lebih lanjut tentang Membuat Daftar Nama Sheet

Buat Daftar Nama Sheet yang Dapat Diklik

Kutools untuk Excel. 300+ fungsi yang harus Anda miliki di Excel, 30 -hari uji coba gratis dari sini

Tampilkan daftar nama lembar kerja yang dinamis dengan Kutools for Excel

Jika Anda Dengan Kutools for Excel, Anda juga bisa menggunakan Navigasi utilitas untuk menampilkan nama lembar kerja yang dapat ditautkan di panel

Setelah menginstal gratis Kutools for Excel, silakan lakukan seperti di bawah ini.

1. Klik Kutools > Navigasi. Klik Buku Kerja & Lembar untuk menampilkan buku kerja dan lembar kerja, dan Anda dapat memilih buku kerja, lalu lembar kerjanya akan ditampilkan di panel Navigasi. Lihat tangkapan layar

Tip

Bila ada lembar kerja yang dihapus atau ditambahkan, Anda dapat mengklik tombol segarkan 

Klik di sini untuk informasi lebih lanjut tentang Navigasi

Panel Navigasi -- Mencantumkan Lembar

Alat Produktivitas Kantor Terbaik

Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%

• Gunakan kembali. Masukkan dengan cepat rumus kompleks, bagan  dan apa pun yang pernah Anda gunakan sebelumnya; . Encrypt Cells with password; Create Mailing List and send emails...
• Super Formula Bar (mengedit beberapa baris teks dan formula dengan mudah); . Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Menggabungkan Sel/Baris/Kolom tanpa kehilangan Data; . Cegah Sel Duplikat; . Combine Duplicate Rows/Columns.. Prevent Duplicate Cells; Compare Ranges...
• Pilih Baris Duplikat atau Unik ; . Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Beberapa Sel tanpa mengubah referensi rumus; . Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Ekstrak Teks , Tambah Teks, Hapus berdasarkan Posisi, Hapus Spasi ; . Convert Between Cells Content and Comments...
• Filter Super (simpan dan terapkan skema filter ke sheet lain); . Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Menggabungkan Buku Kerja dan Lembar Kerja ; . Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• Lebih dari 300 fitur canggih . Mendukung Office / Excel 2007-2021 dan 365. Mendukung semua bahasa. Penerapan yang mudah di perusahaan atau organisasi Anda. Fitur lengkap Uji coba gratis 30 hari. Jaminan uang kembali 60 hari.

Baca selengkapnya. Download Gratis. Pembelian.  

Office Tab Menghadirkan antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Lebih Mudah

• Aktifkan pengeditan dan pembacaan bertab di Word, Excel, PowerPoint , Publisher, Access, Visio, dan Project.
• Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru
• Meningkatkan produktivitas Anda hingga 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari

Baca selengkapnya. Download Gratis. Pembelian.  

Terlama Pertama

Urutkan komentar menurut

Terlama Pertama

Pertama Terbaru

Komentar ( 13 )

Belum ada peringkat. Jadilah yang pertama menilai

Mat

sekitar 6 tahun yang lalu

Komentar ini diminimalkan oleh moderator di situs

Bagaimana cara saya mengedit kode ini untuk menyertakan kotak centang di sebelah kiri tautan yang dapat saya gunakan untuk menyembunyikan baris pada lembar sampul?

Membalas

0

0

Nash

sekitar 6 tahun yang lalu

Komentar ini diminimalkan oleh moderator di situs

Bagaimana saya bisa menghapus tautan ke setiap lembar dan bagaimana cara tidak menyertakan lembar "sheet1" dan "Indeks"? . Pembaruan Layar = Salah xRow = 1 Dengan Saya. Kolom(1). Isi yang jelas. Sel(1, 1) = "INDEX". Sel(1, 1). Nama = "Indeks" Diakhiri Dengan Untuk Setiap xSheet Di Aplikasi. Lembar kerja Jika xSheet. Beri nama Saya. Nama Kemudian xRow = xRow + 1 Dengan xSheet. Rentang("A1"). Nama = "Mulai_" & xSheet. Indeks. Hyperlink. Tambahkan jangkar. =. Rentang ("A1"), Alamat. = "", _ SubAlamat. = "Indeks", TextToDisplay. = "Kembali ke Indeks" Akhiri Dengan Saya. Hyperlink. Tambahkan jangkar. = Saya. Sel (xRow, 1), Alamat. = "", _ SubAlamat. = "Mulai_" & xSheet. Indeks, TextToDisplay. = xLembar. Nama Akhir Jika Aplikasi Selanjutnya. Pembaruan Layar = True End Sub

Membalas

0

0

Cindy

sekitar 5 tahun yang lalu

Komentar ini diminimalkan oleh moderator di situs

Terima kasih. Itu sangat berguna. . D

Membalas

0

0

j4h54n84

sekitar 5 tahun yang lalu

Komentar ini diminimalkan oleh moderator di situs

bagaimana saya bisa menggunakan kode ini untuk membuat daftar lembar kerja dalam urutan horizontal, bukan vertikal?

Membalas

0

0

KP

sekitar 5 tahun yang lalu

Komentar ini diminimalkan oleh moderator di situs

Mendefinisikan nama "Lembar" dalam nama yang ditentukan TIDAK BEKERJA. Kotak "merujuk ke" tidak akan menyimpan rumus saat diketik dan mengubahnya menjadi string teks

Membalas

0

0

PB

sekitar 5 tahun yang lalu

Komentar ini diminimalkan oleh moderator di situs

Apa yang saya butuhkan dan bekerja dengan sempurna. Terima kasih

Membalas

0

0

Ed Bower

sekitar 5 tahun yang lalu

Komentar ini diminimalkan oleh moderator di situs

Atau Anda cukup mengklik kanan panah gulir horizontal tab kiri atau kanan di kiri bawah, lalu klik nama lembar kerja (dari daftar vertikal sederhana) yang ingin Anda lompati

Membalas

0

0

Kelly

sekitar 4 tahun yang lalu

Komentar ini diminimalkan oleh moderator di situs

Terima kasih atas info yang bermanfaat ini. Saya membuat daftar dinamis dengan kode VBA di tab Indeks. Saya menemukan bahwa itu hanya diperbarui ketika saya mengklik kembali ke tab Indeks. Apakah ada cara untuk memastikan pembaruan otomatis segera setelah tab baru dibuat? . Terima kasih

Membalas

0

0

Cerah     Kelly

sekitar 4 tahun yang lalu

Komentar ini diminimalkan oleh moderator di situs

Maaf, saya tidak menemukan bahwa kode dapat menyelesaikan pekerjaan Anda

Membalas

Laporan

0

0

Luiz

sekitar 3 bulan yang lalu

Komentar ini diminimalkan oleh moderator di situs

Di português brasileiro, DAPATKAN. WORKBOOK é identified como uma função no Gerenciador de Nomes to criar o nome "Sheets", mas ao reference "Sheets" numa célula, aparece or erro "#NOME?". Tentei bervariasi kali, termasuk dengan sugestão de traduzir GET. LEMBAR KERJA untuk OBTER. LIVRO tidak berfungsi

Bagaimana cara mendapatkan daftar nama sheet di Excel?

Bagaimana cara daftar nama sheet di Excel VBA?

Bagaimana cara membuat daftar nama lembar kerja otomatis di Excel?

Bagaimana cara menemukan nama sheet di makro Excel?