VBA to define a range - worksheet name w spaces

[tuktuk]

Hey there,

I found some code online that works great for defining a named range. My only issue is that occasionally my worksheet name will contain spaces. this throw off the current code. i do know that if i manually go in to the range and add a "'" to the beginning and to the end of the worksheet name it fixes it. unfortunatly i cannot figure out how to add the "'" in the following VBA.

thanks

Sub AddDynamicRangeVertical()
    On Error Resume Next
    Dim sRangeName As String
    Dim n As Name

    If ActiveWorkbook Is Nothing Then Exit Sub

    sRangeName = InputBox("Enter a range name, then push OK. ", _
    "Add Vertical Dynamic Range")

    If sRangeName = "" Then Exit Sub

    sRangeName = Replace(sRangeName, " ", "_")

    ActiveWorkbook.Names.Add Name:=sRangeName, _
    RefersTo:="=OFFSET(" & ActiveSheet.Name & "!" _
    & ActiveCell.Address & ",,,COUNTA(" & ActiveSheet.Name _
    & "!" & Columns(ActiveCell.Column).Address & "))"

    For Each n In ActiveWorkbook.Names
        If n.Name = sRangeName Then Exit Sub
    Next n

    MsgBox Err.Description, , "Invalid Name"

    On Error GoTo 0
End Sub

 

[rjwebgraphix]

This may do the trick...

ActiveWorkbook.Names.Add Name:="'" & sRangeName & "'", _
    RefersTo:="=OFFSET(" & ActiveSheet.Name & "!" _
    & ActiveCell.Address & ",,,COUNTA(" & ActiveSheet.Name _
    & "!" & Columns(ActiveCell.Column).Address & "))"

Notice when adding the name you're inserting a ' with it. As you've figured out, VBA has issues with multi-word sheet names without it.

 

[tuktuk]

hmmmmmmm...getting an error. doesn't the ' have to be part of the " & ActiveSheet.Name & "!"? thanks

 

[Norie]

Try this.

ActiveWorkbook.Names.Add Name:=sRangeName, _
    RefersTo:="=OFFSET('" & ActiveSheet.Name & "'!" _
    & ActiveCell.Address & ",,,COUNTA('" & ActiveSheet.Name _
    & "'!" & Columns(ActiveCell.Column).Address & "))"

 

[tuktuk]

works great....thanks