Excel索引/目录 - 宏 [英] Excel Index / Table of Contents - Macro

问题描述

我正在阅读一些使用宏自动创建内容表的代码。我有一张多张工作簿，每天都添加新的工作表。每个工作表名称是一个唯一的编号，内部有相同的模板。

I was reading up some codes that used a macro to auto-create a table of contents. I have a workbook with multiple sheets and new sheets are being added everyday. Each sheet name is a unique number and has an identical template inside.

我看到的代码只会将索引与工作表名称进行整理。

The codes I saw would only give the option of collating an index with the Sheet Name.

我想知道如果我可以获得一个选项，也可以从每张表单中获取一个或两个值，如下所示：

I was wondering if I could get an option that would also pick up one or two values from each sheet, like this:

Sr编号单元名称单元格A1单元格A2单元格A3

Sr No. Sheet Name Cell A1 Cell A2 Cell A3

我需要每次打开索引表并将图表名称列为链接到工作表。

I need the Macro to run every time the Index sheet is opened and the Sheet Name column to be hyper-linked to the worksheet.

任何想法？

提前感谢任何帮助！

推荐答案

这段代码基于 http://www.ozgrid.com/VBA/sheet-index.htm 。

在修改后的代码中，列1将包含纸张索引，列2将显示所有WorkSheets的单元格A2和A3的提取。小心，索引表的第1列，第2列和第3列的旧内容将被完全擦除!!!

In the modified code, Column 1 will contain index of sheets, Column 2 will show extracts of cells A2 and A3 for all WorkSheets. Be careful, the old contents of Columns 1, 2 and 3 of the index sheet will be completely erased!!!

Private Sub Worksheet_Activate()

  Dim wSheet As Worksheet

  Dim i As Long

  i = 1
  With Me
    .Columns(1).ClearContents
    .Columns(2).ClearContents
    .Columns(3).ClearContents
    .Cells(1, 1) = "INDEX"
    .Cells(1, 1).Name = "Index"
  End With

  For Each wSheet In Worksheets

    If wSheet.Name <> Me.Name Then
      i = i + 1
      '
      ' in the WorkSheet: set up link to return to the index sheet from:
      '
      With wSheet
        .Range("A1").Name = "Start" & wSheet.Index
        .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
          SubAddress:="Index", TextToDisplay:="Back to Index"
      End With
      '
      ' in the Index WorkSheet: set up link to go to WorkSheet:
      '
      Me.Hyperlinks.Add Anchor:=Me.Cells(i, 1), Address:="", _
        SubAddress:="Start" & wSheet.Index, _
        TextToDisplay:=wSheet.Name
      '
      Me.Cells(i, 2).Value = wSheet.Range("A2").Value
      Me.Cells(i, 3).Value = wSheet.Range("A3").Value
      '
    End If

  Next wSheet

End Sub

这篇关于Excel索引/目录 - 宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！