Marcus131975
New Member
- Joined
- Feb 9, 2018
- Messages
- 14
Hi
I found some VBA online which achieved what I wanted to (created an index page with links to each sheet plus a reference column). However, the links dont work if the sheet name has a space in. I could really do with keeping the spaces in, so wondered if there was a quick fix? I'd be grateful for any help -an extract of some of the code is below, which I assume is where the issue is? I have seen references to adding single quotes but couldnt figure out where.
'Loop through all sheets
For Each ws In wb.Worksheets
If ws.Name <> wsTOC.Name Then
If ws.Visible = True Then
With wsTOC
.Range("B" & r) = ws.Name
.Range("E" & r) = ws.Range("CA1")
End With
End If
If ws.Visible = True Then
wsTOC.Hyperlinks.Add anchor:=wsTOC.Cells(r, 2), Address:="", _
SubAddress:=ws.Name & "!A1"
End If
End If
r = r + 1
Next ws
lngRows = wsTOC.Cells(Rows.Count, 2).End(xlUp).Row 'Find last Row
With wsTOC
.Columns("B:B").ColumnWidth = 34
I found some VBA online which achieved what I wanted to (created an index page with links to each sheet plus a reference column). However, the links dont work if the sheet name has a space in. I could really do with keeping the spaces in, so wondered if there was a quick fix? I'd be grateful for any help -an extract of some of the code is below, which I assume is where the issue is? I have seen references to adding single quotes but couldnt figure out where.
'Loop through all sheets
For Each ws In wb.Worksheets
If ws.Name <> wsTOC.Name Then
If ws.Visible = True Then
With wsTOC
.Range("B" & r) = ws.Name
.Range("E" & r) = ws.Range("CA1")
End With
End If
If ws.Visible = True Then
wsTOC.Hyperlinks.Add anchor:=wsTOC.Cells(r, 2), Address:="", _
SubAddress:=ws.Name & "!A1"
End If
End If
r = r + 1
Next ws
lngRows = wsTOC.Cells(Rows.Count, 2).End(xlUp).Row 'Find last Row
With wsTOC
.Columns("B:B").ColumnWidth = 34