Referencing sheets with spaces

Question

I am having an issue with referencing the sheet name through =Branded!$A$1 Notation in VBA. For a while I have passed in simple sheet names like:

Dim SheetName As String
SheetName = "Pizza"
("=" & SheetName & "!$A$1")

This has worked fine, but recently I passed in "Tier 1" and of course this notation broke. Is there any fix or workaround for this? It Think it's because of the space, the number or both....

You are declaring SheetName as an Integer and assigning a string to it. This code triggers an error. — varocarbas, Jul 22 '13 at 13:33
Sheet names with spaces require single-quotes. Create one, and then refer to a cell in it from another sheet and you'll see the correct format with the name surrounded by single-quotes. — Doug Glancy, Jul 22 '13 at 13:57

score 3 · Answer 1 · answered Jul 22 '13 at 14:05

3

varocarbas is right, if you declare "As Integer" it means you will put numbers not text. Just change "As Integer" to "As String" and it should look something like this:

Sub Reference()

Dim SheetName As String
SheetName = "Pizza 1"
Range("A1").Value = "='" & SheetName & "'!A1"

End Sub

answered Jul 22 '13 at 14:05

lowak

1,254
2
18
38

1

@user2250964 - then update your question with your *actual* code – LittleBobbyTables - Au Revoir Jul 22 '13 at 15:05
1

I just don't know what this code stands for? ("=" & SheetName & "!$A$1") Could you explain? – lowak Jul 22 '13 at 20:12

score 0 · Answer 2 · answered Jul 22 '13 at 13:57

0

no idea what your code is meant to do but if you have spaces in a sheet name (or a name that looks like a date) you have to enclose it in single quotes

"='" & SheetName & "'!$A$1"

answered Jul 22 '13 at 13:57

JosieP

3,360
1
13
16

Referencing sheets with spaces

2 Answers2