|
Option Explicit
Sub test1()
Dim ar, br, i&, j&, wks As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ar = Range("A2", Cells(Rows.Count, "AH").End(xlUp)).Value
ReDim br(1 To 31, 1 To 2)
Set wks = Worksheets("项目模版")
With Workbooks.Add
For i = 2 To UBound(ar)
wks.Copy after:=.Worksheets(.Worksheets.Count)
With ActiveSheet
.Name = ar(i, 2)
.Cells(2, 2) = ar(i, 2)
.Cells(2, 8) = ar(i, 3)
.Cells(39, 4) = ar(i, 2)
For j = 1 To UBound(br)
br(j, 1) = ar(1, j + 3)
br(j, 2) = ar(i, j + 3)
Next j
.Cells(5, 1).Resize(31, 2) = br ' ar(i, 9)
End With
Next
For Each wks In .Worksheets
If wks.Name Like "*Sheet*" Then wks.Delete
Next
End With
Call CreateList
Set wks = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Beep
End Sub
Sub CreateList()
Dim i&
Worksheets.Add(before:=Worksheets(1)).Name = "工作表目录"
[A1].Resize(, 2) = [{"序号", "姓名"}]
For i = 2 To Worksheets.Count
Cells(i, 1).Value = i - 1
ActiveSheet.Hyperlinks.Add Cells(i, 2), "", "'" & Worksheets(i).Name & "'" & "!A1", _
"单击打开:" & Worksheets(i).Name, Worksheets(i).Name
Worksheets(i).Hyperlinks.Add Worksheets(i).Cells(1, 1), "", _
Worksheets(1).Name & "!B" & i, "返回目录"
With Worksheets(i).Cells(1, 1)
.Font.Size = 22
.Font.Bold = True
End With
Next i
Columns("A:D").AutoFit
End Sub
|
|