Monthly Archives: September 2013

ok something completle different… vba… currently i am working in a small person related company, where they manage their clients in an Excel sheet. This actually is a good tool, but every person has his own sheet, where searching is horrible… so my goal is to write a small script, to generate an index page.

Sub SheetNames()

‘ Erster Eintrag auf Zeile
StartRow = 3

‘ Laufvariable mit der Postion zum einfüllen des aktuellen SheetNamens
Position = StartRow

For i = 1 To Sheets.Count

‘ Nur einfüllen, wenn der SheetName nicht dem Inhaltsverzeichnis entspricht

If Sheets(i).Name <> ActiveSheet.Name Then

‘Link erstellen
‘=HYPERLINK(“[Makro Tester.xlsm]’Tabelle1’!A1”; “Tabelle1”)
Worksheets(“Index”).Cells(Position, 1) = “=HYPERLINK(“”[” & ThisWorkbook.Name & “]'” & Sheets(i).Name & “‘!A1″”, “”” & Sheets(i).Name & “””)”

‘Position weiterzählen
Position = Position + 1
End If

Next i

‘ Leere Zeilen am Ende einfügen, um gegebenenfalls gelöschte Sheets aus dem Index zu löschen
For i = 1 To 5
Worksheets(“Index”).Cells(Position, 1) = “”

‘ Position weiterzählen
Position = Position + 1
Next i
End Sub