批量创建超链接工作表并自动更新-批量新建工作表并添加超链接

如何在工作簿中批量建立多个工作表并用超链接链接到对应的工作表？这里我们用VBA来循环建立工作表

首先，要在一个工作薄中批量建立多个工作表，这里先做几个模板，然后需要哪个就复制哪个。

一）右键点击工作表标签，选择查看代码。

二）写入下面的代码。

Dim a As Byte, b As Byte, c As Byte, d As Byte

Dim lujin As String, wb As Workbook, arr As Range, str As String

Sub xjclj()

    a = Range(ThisWorkbook.Sheets(1).Cells(200, 2), ThisWorkbook.Sheets(1).Cells(200, 2).End(xlUp)).Row

    lujin = ThisWorkbook.Path & "MQ.xls"

    Set wb = Workbooks.Add

    wb.Sheets(1).Name = "目录"

    wb.Sheets(3).Delete

    wb.Sheets(2).Delete

    For b = 2 To a

        c = ThisWorkbook.Sheets(1).Cells(b, 3)

        ThisWorkbook.Sheets(c).Copy After:=wb.Sheets(b - 1)

        wb.Sheets(b).Name = ThisWorkbook.Sheets(1).Cells(b, 2)

    Next

    d = 2

    For Each arr In wb.Sheets(1).Range("A1").Resize(a - 1, 1)

        str = wb.Sheets(d).Name

        wb.Sheets(1).Hyperlinks.Add Anchor:=arr, Address:="", SubAddress:=str & "!A1", TextToDisplay:=str

        d = d + 1

    Next

    wb.SaveAs lujin

End Sub

三）运行程式。运行效果