Daftar Isi Makro - Tip Excel

Terima kasih kepada Matt yang mengirimkan pertanyaan Excel minggu ini:

Saya memiliki buku kerja Excel yang besar dan terus bertambah (banyak lembar). Saya telah menyertakan nomor halaman di footer saat mencetak, namun semakin sulit untuk dinavigasi saat kita sedang rapat. Apakah ada cara untuk mencetak daftar isi berdasarkan nama lembar kerja Excel sehingga saya dan staf dapat dengan cepat membuka halaman #xx?

Ini ide yang bagus. Saran sederhana pertama adalah memasukkan nama lembar di footer cetakan Anda. Saat Anda mengklik "Footer Kustom" di dialog Page Setup / Header Footer, ada 7 ikon. Ikon paling kanan tampak seperti kartu indeks dengan tiga tab. Mengklik di bagian Kanan: kotak dan menekan ikon itu akan menyebabkan nama lembar dicetak pada setiap lembar. Ini saja dapat membantu menavigasi laporan.

MrExcel menyukai gagasan memiliki makro untuk membuat daftar isi. Masalah utamanya adalah Excel tidak menghitung berapa banyak halaman yang dicetak pada lembar kerja sampai Anda melakukan pratinjau cetak. Jadi, makro memungkinkan pengguna mengetahui bahwa mereka akan melihat Pratinjau Cetak dan meminta mereka untuk menutupnya dengan mengklik tombol tutup.

Makro mengulang melalui setiap lembar di buku kerja. Dalam keadaannya saat ini, ia mengumpulkan informasi dari nama setiap lembar kerja. Saya juga menyertakan dua baris lain yang diberi komentar. Jika Anda lebih suka mendapatkan deskripsi dari header kiri atau dari judul di sel A1, ada contoh baris untuk melakukan salah satunya juga. Cukup hapus komentar yang ingin Anda gunakan.

Makro menghitung berapa banyak halaman dengan menambahkan satu ke jumlah jeda halaman horizontal (HPageBreaks.count). Ia menambahkan satu ke jumlah jeda halaman vertikal (VPageBreaks.Count). Ini mengalikan dua angka ini bersama-sama untuk menghitung jumlah halaman pada lembar kerja itu. Jika ada pembaca setia yang memiliki cara yang lebih baik untuk melakukan ini, beri tahu saya. Metode penghitungan jeda halaman saat ini sangat lambat. Sepertinya saya tidak dapat menemukan properti yang memberi tahu saya berapa banyak halaman yang dicetak, tetapi Anda akan mengira Excel akan menyertakannya.

Trik terakhir adalah memasuki rentang halaman. Jika sheet berada di halaman "3 - 4", Excel akan memperlakukan ini sebagai tanggal dan memasukkan 4 Maret. Dengan mengatur format sel menjadi teks dengan karakter "@", halaman masuk dengan benar.

Berikut makro:

Sub CreateTableOfContents() ' Copyright 1999.com ' Determine if there is already a Table of Contents TOCFound = False For Each s In Worksheets If s.Name = "Table of Contents" Then TOCFound = True Exit For End If Next s If Not TOCFound Then Sheets.Add Before:=Worksheets(1) ActiveSheet.Name = "Table of Contents" End If ' Set up the table of contents page TOCRow = 7 PageCount = 0 Sheets("Table of Contents").Select Range("A2").Value = "Table of Contents" Range("A6").CurrentRegion.Clear Range("A6").Value = "Subject" Range("A6").ColumnWidth = 36 Range("B6").Value = "Page(s)" Range("B6").ColumnWidth = 12 ' Do a print preview on all sheets so Excel calcs page breaks ' The user must manually close the PrintPreview window Worksheets.Select Msg = "Excel needs to do a print preview to calculate the number of pages. " Msg = Msg & "Please dismiss the print preview by clicking close." Msgbox Msg ActiveWindow.SelectedSheets.PrintPreview ' Loop through each sheet, collecting TOC information For Each s In Worksheets s.Select ' Use any one of the following 3 lines ThisName = ActiveSheet.Name 'ThisName = Range("A1").Value 'ThisName = ActiveSheet.PageSetup.LeftHeader HPages = ActiveSheet.HPageBreaks.Count + 1 VPages = ActiveSheet.VPageBreaks.Count + 1 ThisPages = HPages * VPages ' Enter info about this sheet on TOC Sheets("Table of Contents").Select Range("A" & TOCRow).Value = ThisName Range("B" & TOCRow).NumberFormat = "@" If ThisPages = 1 Then Range("B" & TOCRow).Value = PageCount + 1 & " " Else Range("B" & TOCRow).Value = PageCount + 1 & " - " & PageCount + ThisPages End If PageCount = PageCount + ThisPages TOCRow = TOCRow + 1 Next s End Sub

Di bawah ini adalah makro yang setara, diperbarui dengan beberapa teknik makro baru.

Sub CreateTableOfContents() ' Copyright 2002.com ' Determine if there is already a Table of Contents ' Assume it is there, and if it is not, it will raise an error ' if the Err system variable is> 0, you know the sheet is not there Dim WST As Worksheet On Error Resume Next Set WST = Worksheets("Table of Contents") If Not Err = 0 Then ' The Table of contents doesn't exist. Add it Set WST = Worksheets.Add(Before:=Worksheets(1)) WST.Name = "TOC" End If On Error GoTo 0 ' Set up the table of contents page WST.(A2) = "Table of Contents" With WST.(A6) .CurrentRegion.Clear .Value = "Subject" End With WST.(B6) = "Page(s)" WST.Range("A1:B1").ColumnWidth = Array(36, 12) TOCRow = 7 PageCount = 0 ' Do a print preview on all sheets so Excel calcs page breaks ' The user must manually close the PrintPreview window Msg = "Excel needs to do a print preview to calculate the number of pages. " Msg = Msg & "Please dismiss the print preview by clicking close." MsgBox Msg ActiveWindow.SelectedSheets.PrintPreview ' Loop through each sheet, collecting TOC information ' Loop through each sheet, collecting TOC information For Each S In Worksheets If S.Visible = -1 Then S.Select ' Use any one of the following 3 lines ThisName = ActiveSheet.Name 'ThisName = Range("A1").Value 'ThisName = ActiveSheet.PageSetup.LeftHeader HPages = ActiveSheet.HPageBreaks.Count + 1 VPages = ActiveSheet.VPageBreaks.Count + 1 ThisPages = HPages * VPages ' Enter info about this sheet on TOC Sheets("TOC").Select Range("A" & TOCRow).Value = ThisName Range("B" & TOCRow).NumberFormat = "@" If ThisPages = 1 Then Range("B" & TOCRow).Value = PageCount + 1 & " " Else Range("B" & TOCRow).Value = PageCount + 1 & " - " & PageCount + ThisPages End If PageCount = PageCount + ThisPages TOCRow = TOCRow + 1 End If Next S End Sub

Ringkasan singkat tentang teknik makro baru di makro yang lebih baru:

  • Jarang perlu memilih lembar
  • Daripada mengulang setiap lembar di buku kerja mencari lembar yang disebut Daftar Isi, makro ke-2 mengasumsikannya ada di sana dan memeriksa status variabel Err. Jika Err adalah selain 0, kita tahu bahwa sheet tersebut tidak ada dan perlu ditambahkan.
  • WST adalah variabel objek dan didefinisikan sebagai lembar kerja Daftar Isi. Jadi, setiap referensi ke Lembar Kerja ("Daftar Isi"). bisa diganti dengan WST.
  • Konstruksi Sel (baris, kolom) lebih efisien daripada kluge Range ("A" & TOCRow). Karena Cells () mengharapkan parameter numerik, Range ("A" & TOCRow) menjadi sel (TOCRow, 1)
  • Tanda kurung siku digunakan sebagai cara singkatan untuk merujuk ke Range ("A1").

Artikel yang menarik...