Option Explicit
Sub Test()
Worksheets.Add.Name = "MeinBlatt.1"
Worksheets.Add.Name = "MeinBlatt.2"
Worksheets.Add.Name = "MeinBlatt.3"
Tabelle1.Range("A2").Value = "MeinBlatt.1"
Tabelle1.Range("A3").Value = "MeinBlatt.2"
Tabelle1.Range("A4").Value = "MeinBlatt.3"
Tabelle1.Activate
Dim rngCell As Excel.Range
Set rngCell = Tabelle1.Range("A2")
Do While rngCell.Value <> ""
rngCell.Formula = "=HYPERLINK(""#'" & rngCell.Value & "'!A1"",""" & rngCell.Value & """)"
Set rngCell = rngCell.Offset(1)
Loop
End Sub