Spaces in sheet name stops macro

[JonatMV]

Hi - I've had a macro running for ages, and just found it bugs out when there are spaces in a sheet name.

The user selects a destination cell as part of a form, which passes the variable to the following steps:

looperx = 0
Do
looperx = looperx + 1
Loop Until Mid(celldestinationstring, looperx, 1) = "!"
Sheets(Left(celldestinationstring, looperx - 1)).Select

The purpose of which is to isolate the sheet name from the cell reference, which is typically like: 'Bob Sheet'!$B$9

This works fine where there are no spaces, but 'dies' with a runtime error when there are. With the apostrophes I assumed it would work around spaces in the string but I can't get it to work.

Appreciate any help... I'm thinking it's probably something simple.

 

[RoryA]

If you just want to select the sheet for some reason, I would use:

Range(celldestinationstring).Worksheet.Select

 

[Norie]

The problem is probably the 's.

You wouldn't use them when referring to a worksheet by name in VBA.

pos = InStr(1, celldestination, "!")
 
celldestination = Left(celldestination, pos-1)
 
celldestination = Replace(celldestination, "'", "")

Sheets(celldestination).Select

 

[Richard Schollar]

You could just extract the sheet directly:

Range(celldestinationstring).Parent.Select

Note that you don't usually have to Select anything to work with it and doing so is inefficient.

 

[JonatMV]

Thanks all for your responses.

I realised after reading some of the replies that I'd written this some time ago and it could well be optimised somewhat (InStr being a good example!)

I used Richard's in the end as it was a one liner which I quite liked. From memory - I chose to select the sheet so the user ends up looking at the output of the activesheet.querytable that is created subsequently... albeit admittedly it may be a redundant step as I've not tested without it.

Cheers again.

 

[Richard Schollar]

I used Richard's in the end as it was a one liner which I quite liked.

Good choice! Rory's was a single line as well, but I'd go with mine too as nobody ever calls me Mary

 

[RoryA]

That's true, Wendy, we don't.

 

[Richard Schollar]

Ooh you beast!!! Handbags at dawn and if you think I'm doing your blue-rinse next week, forget it!

 

[JonatMV]

heh! Yes; they're both one liners but I've not used the .parent syntax before (the bit I liked)