Macro to create link to nagivate through worksheet

[kaffal]

Hi all ,
I have a workbook with about 20 to 50 sheets.
Inside the workbook has a worksheet, named Summary, which will help to summarize up all the worksheet.

I would like to create a table with the following info extract from each sheet.
Column 1 : Tabname of each worksheet except Summary
Column 2 : Return a count on how many "completed" are found in column H in every sheet except for the Summary worksheet.

For column 3 : I would like to have a link to navigate to the respective worksheet.

 

[VoG]

This will create a new index sheet:

Sub IdxSheets()
Dim ws As Worksheet, iws As Worksheet, i As Integer
Set iws = Worksheets.Add
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> iws.Name Then
        i = i + 1
        iws.Range("A" & i).Value = ws.Name
        iws.Range("B" & i).Value = WorksheetFunction.CountIf(ws.Columns("H"), "Completed")
        iws.Hyperlinks.Add Anchor:=iws.Range("C" & i), Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
    End If
Next ws
iws.Columns("A").AutoFit
End Sub

 

[kaffal]

Cools, It works !
But I would like to add on something if you dun mind.

- i would like to exclude the Menu Worksheet in the list created
- Instead of counting "Completed" in Column H, I would like to include counting from column C to column Z .
- Instead of creating a new sheet, It will start the list in a worksheet,"Summary" from Cell A2 onward

 

[VoG]

Try

Sub IdxSheets()
Dim ws As Worksheet, iws As Worksheet, i As Integer
Set iws = Worksheets.Add
On Error Resume Next
iws.Name = "Summary"
i = 1
With iws.Range("A1:C1")
    .Value = Array("Sheet", "Completed", "Link")
    .Font.Bold = True
End With
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> iws.Name And ws.Name <> "Menu" Then
        i = i + 1
        iws.Range("A" & i).Value = ws.Name
        iws.Range("B" & i).Value = WorksheetFunction.CountIf(ws.Columns("C:Z"), "Completed")
        iws.Hyperlinks.Add Anchor:=iws.Range("C" & i), Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
    End If
Next ws
iws.Columns("A:C").AutoFit
End Sub