[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

Create macro in excel to show list of all tabs in the excel workbook

Dear Expert,

Needing macro that:
1. Creates a new worksheet as first sheet in the workbook.
2. Lists all worksheets, with a hyperlink on each listed worksheet on the list that takes to the A1 cell of that respective worksheet.

Br,
JP
0
easycapital
Asked:
easycapital
  • 2
2 Solutions
 
Rgonzo1971Commented:
Hi,

pls try

Sub macro()
Set shSummary = ActiveWorkbook.Sheets.Add(before:=Sheets(1))
shSummary.Name = "Summary"
Idx = 1
For Each sh In ActiveWorkbook.Worksheets
    If sh.Name <> "Summary" Then
        shSummary.Hyperlinks.Add Anchor:=shSummary.Range("A" & Idx), _
            Address:="", SubAddress:=sh.Name & "!" & "A1", TextToDisplay:=sh.Name
        Idx = Idx + 1
    End If
Next
End Sub

Open in new window

Regards
0
 
Glenn RayExcel VBA DeveloperCommented:
Hi, this code will re-build an Index sheet even if one already exists:
Option Explicit
Sub Build_Index()
    Dim ws, wsIndex As Worksheet
    Dim x As Integer
    Application.DisplayAlerts = False
    Sheets(1).Select
    If Sheets(1).Name = "Index" Then Sheets("Index").Delete
    Sheets.Add
    Sheets(1).Name = "Index"
    Set wsIndex = Sheets("Index")
    Range("A1").Select
    x = 1
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Index" Then
            wsIndex.Hyperlinks.Add Anchor:=wsIndex.Cells(x, 1), _
                Address:="", SubAddress:="'" & ws.Name & "'!A1", _
                TextToDisplay:=ws.Name
            x = x + 1
        End If
    Next ws
    Application.DisplayAlerts = True
End Sub

Open in new window


Regards,
-Glenn
EE-CreateIndex.xlsm
0
 
Glenn RayExcel VBA DeveloperCommented:
Hi,

Did you have a chance to review and or test our solutions?  If so, and either/both will work for you, can you please properly close this question by clicking the "Accept this solution" link(s) above the submission(s) that answers your question?.  This will help ensure that future searches are meaningful to other EE members.

Otherwise, let us know if you have any other issues.

Thanks,
 -Glenn
0
 
easycapitalAuthor Commented:
Sorry. I just landed and back from traveling.
Br,
JP
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now