Excel VBA help for creating hyperlinked list of all worksheeets but the first two

[michaelwhissel]

Greetings,

I need Excel VBA help for creating hyperlinked list of all worksheets but the first two.

In Excel 2010, I am using a large macro (bare bones version below), which when run from sheet(1), "Index," does the following: reads a list of values from sheet(1), col A, starting on A3; creates a worksheet for each value; names worksheets per col A values; and creates a hyperlinked list of worksheets in sheet(1), Col A, starting on A3 (over-writes what was there).

Here's the issue: I do not want sheet(1) "Index," or sheet(2), "ADMIN," included in the hyperlinked
list.

I know why the current code does what is does, but I have not been able to come up with the fix.

Your ideas are appreciated! Thanks I advance!

---

Current Code:

Sub CreateSheets()

Dim MyCell As Range, MyRange As Range
Dim sht As Worksheet
Dim ThisSheet As Worksheet
Application.ScreenUpdating = False
Set ThisSheet = ActiveSheet
Set MyRange = Sheets("Index").Range("A3")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
​

Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
​

Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
​

Next MyCell
​

ThisSheet.Select
Set ThisSheet = Nothing
Dim sh As Worksheet
Dim cell As Range
Range("A3").Select

For Each sh In ActiveWorkbook.Worksheets
​

If ActiveSheet.Name <> sh.Name Then
​

ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
​

"'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
​

ActiveCell.Offset(1, 0).Select
​

End If
​

Next sh
​

Application.ScreenUpdating = True
End Sub

 

[jbeaucaire]

I would think something like this:

    For Each sh In ActiveWorkbook.Worksheets
        If UCASE(sh.Name) <> "ADMIN" And UCase(sh.Name) <> "INDEX" Then

 

[michaelwhissel]

Many Thanks, jbeaucaire!! Worked like a charm!

 

[michaelwhissel]

jbecaire,

As an FYI --

Sorry, I am rather new to Excel VBA (I only have light programming, some Basic, a fair amount of ColdFusion from years ago).

While the code you were thinking of worked fine as-is, it didn't work if I changed ADMIN to MasterRoster. WHY?!

It turned out there are two things going on:

1. the UCASE is a non-issue in this particular case. It just converts a string to caps (I knew that but forgot).

2. the macro variables for worksheets apparently must appear in the same order in which they appear in the spreadsheet.

So, while this did not work:
(sheet2 argument, Sheet1 argument):

If (sh.Name) <> "MasterRoster" And (sh.Name) <> "INDEX" Then​

This did work:
(sheet1 argument, sheet2 argument):

If (sh.Name) <> "INDEX" (sh.Name) <> "MasterRoster" Then
​

Thanks for the huge head start! You helped me go in the right direction!

I'd appreciate comments on Excel VBA argument order from old pros!