CODEWITHSUNDEEP
excelvba
Horby
Horby

Reputation: 45

List all sheets with link

How can create a index for all sheets automatically, I have a lot customers, with a sheet for each customer.

I also want to show some of the data in index of all the sheets.

see images image

images

Upvotes: 0

Views: 311

Answers (1)

MatthewD
MatthewD

Reputation: 6801

Loop through the sheets creating a link for each. This will list them starting at the active cell.

Sub CreateLinksToAllSheets()
Dim sh As Worksheet
Dim cell As Range
Dim strLink As String

For Each sh In ActiveWorkbook.Worksheets

    If ActiveSheet.name <> sh.name Then

        strLink = sh2.name
        If InStr(strLink, "'") Then
            strLink = Replace(strLink, "'", "''")
        End If

        ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & strLink & "'" & "!A1", TextToDisplay:=sh.name

        ' Here you can get the amount and mobile and write them to the adjacent columns.
        ActiveCell.Offset(0, 1).Value = Excel.worksheetFunction.Sum(sh.Range("A:A"))
        ActiveCell.Offset(0, 2).Value = sh.Range("G1").Value

        ActiveCell.Offset(1, 0).Select

    End If

Next sh

End Sub

If you want to hard code where the links go you can do it like this.

Sub CreateLinksToAllSheets()
Dim sh As Worksheet
Dim sh2 As Worksheet
Dim cell As Range
Dim lRow As Long

    Set sh = ActiveWorkbook.Sheets("Sheet1")
    lRow = 1

    For Each sh2 In ActiveWorkbook.Worksheets

        If ActiveSheet.name <> sh2.name AND sh2.name <> "new customer" AND sh2.name <> "old archive" Then

            strLink = sh2.name
            If InStr(strLink, "'") Then
                strLink = Replace(strLink, "'", "''")
            End If

            sh.Hyperlinks.Add Anchor:=sh.Range("A" & lrow), Address:="", SubAddress:="'" & strLink & "'" & "!A1", TextToDisplay:=sh2.name
            sh.Range("B" & lRow).Value = Excel.worksheetFunction.Sum(sh2.Range("A:A"))
            sh.Range("C" & lRow).Value = sh2.Range("G1").Value

            lRow = lRow + 1

        End If

    Next sh2

End Sub

EDIT: OP asks
How can I make the value in column B and C Value be renewed from within sheets? Example: Column B: =SUMMA(Customer1!A:A) Column C: =Customer1!G1

You can use the formula property. Insert the sheet name from the current sh2 worksheet by using sh2.Name.

 sh.Range("B" & lRow).Formula = "=SUM(" & sh2.Name & "!A:A)"
 sh.Range("C" & lRow).Formula = "=" & sh2.Name & "!G1"

EDIT: OP found that creating a hyperlink to a sheet that has an apostrophe in the name does not create a functioning link.

To fix this we can add a check for the existence of an apostrophe in the sheet name. If we find one we will replace it with two apostrophes.

strLink = sh2.name
If InStr(strLink, "'") Then
     strLink = Replace(strLink, "'", "''")
End If

And change the hyperlinks.add to create SubAddress:="'" & strLink & "'"

sh.Hyperlinks.Add Anchor:=sh.Range("A" & lrow), Address:="", SubAddress:="'" & strLink & "'" & "!A1", TextToDisplay:=sh2.name

Upvotes: 2

Related Questions