VBA.Mania

Public Sub SheetIndexWith_wsIndexNumer()
Dim wrsAktivtBlad As Worksheet
Dim wrbBok As Workbook
Dim ws As Worksheet
Dim iRow As Long
Dim sh$

On Error Resume Next
sh = Sheets("Index").Name
On Error GoTo 0

If sh <> "" Then
Sheets("Calculation").Activate
Else
Worksheets.Add.Name = "Index"
End If

With Sheets("Index")
.Range("A1:A" & CStr(.Rows.Count)).ClearContents
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'.Columns(1).ClearContents
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
End With
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Set wrsAktivtBlad = ActiveSheet
'nie ma pewności czy to jest Index czy Calculation
iRow = 2
For Each ws In Worksheets
    If ws.Name <> "Index" And ws.Name <> "Calculation" And ws.Name <> "Data" Then
    iRow = iRow + 1

        With Sheets("Index")
        .Hyperlinks.Add Anchor:=.Cells(iRow, "A"), Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:="" & ws.Name
        .Hyperlinks.Add Anchor:=.Cells(iRow, "E"), Address:="", SubAddress:="'" & ws.Name & "'!E1", TextToDisplay:="" & ws.Index

            wrsAktivtBlad.Cells(iRow, "B") = ws.Range("E3")
            wrsAktivtBlad.Cells(iRow, "C") = ws.Range("E4")

            If ws.Visible = xlSheetVeryHidden Then
                wrsAktivtBlad.Cells(iRow, "D") = "Very Hidden"
            ElseIf ws.Visible = xlSheetHidden Then
                wrsAktivtBlad.Cells(iRow, "D") = "Hidden"
            ElseIf ws.Visible = xlSheetVisible Then
                wrsAktivtBlad.Cells(iRow, "D") = "Visible"
            End If
        End With
    End If
Next ws
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'poniższe polecenia dotyczą aktywnego arkusza
    Columns("A:E").EntireColumn.AutoFit
    'wystarczy,
    'Columns("A:E").AutoFit
    Columns("C:C").NumberFormat = "m/d/yyyy"

'>>
Cells.FormatConditions.Delete 'dotyczy wszystkich komórek
'unikamy wszelkiego rodzaju selectów, czy koniecznie formatowanie warunkowe musi dotyczyć całej kolumny?
With Columns("D:D")
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""Hidden"""
    With .FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
.FormatConditions(1).StopIfTrue = False
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""Very Hidden"""
    With .FormatConditions(2).Font
        .Bold = True
        .Color = -16776961
        .TintAndShade = 0
    End With
.FormatConditions(2).StopIfTrue = False
End With
'<<

Range("A1").Select
End Sub