stuartgb100
Active Member
- Joined
- May 10, 2015
- Messages
- 322
- Office Version
- 2021
- Platform
- Windows
Hi,
I have a workbook with sheets containing a list of some of my music collection.
Each sheet is named with a specific “Genre”, eg: “Blues” etc.
I have found code which will create an Index sheet as the 1st tab, and import hyperlinks into col A for each sheet in the workbook. This gives me a list of the sheet names as hyperlinks.
This is the code that I found:
Sub CreateMenuOfHyperlinksToAllWorksheets()
Dim objSheet As Worksheet
ActiveWorkbook.Sheets.Add(Before:=Worksheets(1)).Name = "Sheet Menu"
Range("A1").Select
For Each objSheet In ActiveWorkbook.Worksheets
If ActiveSheet.Name <> objSheet.Name Then
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= "'" & objSheet.Name & "'" & "!A1", TextToDisplay:=objSheet.Name
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireColumn.AutoFit
End If
Next objSheet
With ActiveSheet
.Rows(1).Insert
.Cells(1, 1) = "MENU"
.Cells(1, 1).Font.Bold = True
.Cells(1, 1).Font.Size = 14
.Cells(1, 1).Columns.AutoFit
End With
End Sub
However, if it’s possible, what I’d like to do is to import more information into the ‘Sheet Menu’ sheet when the routine runs.
So, in a typical sheet, my data looks like this:
Artists 2 (which is the sheet name)
1. Blues
BB King
BB Blues... The Best Of
01_[I'm Gonna] Quit My Baby.flac
02_A Lonely Lover's Plea.flac
03_Army Of The Lord.flac
04_B.B. Blues.flac
etc.
where
Artists 2’ is in col A
‘Blues’ is in col B
‘BB King’ is in col C
‘BB Blues… The Best of’ is in col D
Is it possible to do as follows, please:
And then cycle through the rest of the sheet.
A bit convoluted, but hope it makes sense !
If not, please ask for clarification.
Many thanks for your attention.
Cheers.
I have a workbook with sheets containing a list of some of my music collection.
Each sheet is named with a specific “Genre”, eg: “Blues” etc.
I have found code which will create an Index sheet as the 1st tab, and import hyperlinks into col A for each sheet in the workbook. This gives me a list of the sheet names as hyperlinks.
This is the code that I found:
Sub CreateMenuOfHyperlinksToAllWorksheets()
Dim objSheet As Worksheet
ActiveWorkbook.Sheets.Add(Before:=Worksheets(1)).Name = "Sheet Menu"
Range("A1").Select
For Each objSheet In ActiveWorkbook.Worksheets
If ActiveSheet.Name <> objSheet.Name Then
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= "'" & objSheet.Name & "'" & "!A1", TextToDisplay:=objSheet.Name
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireColumn.AutoFit
End If
Next objSheet
With ActiveSheet
.Rows(1).Insert
.Cells(1, 1) = "MENU"
.Cells(1, 1).Font.Bold = True
.Cells(1, 1).Font.Size = 14
.Cells(1, 1).Columns.AutoFit
End With
End Sub
However, if it’s possible, what I’d like to do is to import more information into the ‘Sheet Menu’ sheet when the routine runs.
So, in a typical sheet, my data looks like this:
Artists 2 (which is the sheet name)
1. Blues
BB King
BB Blues... The Best Of
01_[I'm Gonna] Quit My Baby.flac
02_A Lonely Lover's Plea.flac
03_Army Of The Lord.flac
04_B.B. Blues.flac
etc.
where
Artists 2’ is in col A
‘Blues’ is in col B
‘BB King’ is in col C
‘BB Blues… The Best of’ is in col D
Is it possible to do as follows, please:
- Run 'my' CreateMenuOfHyperlinksToAllWorksheets() routine to create the ‘Sheet Menu’ worksheet, then before it loops….
- Add ‘Blues’ into cell B2
- Add ‘BB King into cell C3
- Add ‘BB Blues… The Best of’ into cell D4
And then cycle through the rest of the sheet.
A bit convoluted, but hope it makes sense !
If not, please ask for clarification.
Many thanks for your attention.
Cheers.
