Sub Insert_Contents()
Dim MucLuc As Worksheet
Const TenMucLuc As String = "Muc Luc"
Dim sh As Worksheet
Dim row As Integer: row = 5
If Not sheetexists(TenMucLuc) Then
Set MucLuc = ActiveWorkbook.Sheets.Add( _
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.count))
MucLuc.Name = TenMucLuc
Else
MsgBox "Muc luc ton tai, va se duoc update!"
Set MucLuc = ActiveWorkbook.Sheets(TenMucLuc)
With MucLuc
For Each sh In Worksheets
.Cells(row, 1).Formula = "=hyperlink(""#'" & sh.Name & "'!B1"",""" & sh.Name & """)"
If sh.Name <> TenMucLuc Then
sh.[B1].Formula = "=hyperlink(""#'" & TenMucLuc & "'!B1"",sh.[B1].value)"
End If
row = row + 1
Next sh
.Activate
End With
End If
End Sub
Function sheetexists(n As String) As Boolean
sheetexists = False
For Each ws In Worksheets
If n = ws.Name Then
sheetexists = True
Exit Function
End If
Next ws
End Function