Dynamic Range Name Macro

[Jonathan Cooper]

Learning from this comunity, I am using dynamic range names when importing
data through our ODBC connection, and then creating a pivot table that uses
the range name. I works great. In fact, I create new applications like this
frequently; so much so that I've created a macro to automate the creation of
the dynamic range name. The problem I run into is when the SHEET name has a
space in it.

If there is no space in the name when I create it, everything works fine.
How can I adjust this macro to accomodate a space in the sheet name.

Example,
If the sheet name is 'MyData', then it works fine. But if it is 'My Data'
then the macro doesn't work.


Keep in mind that I've just cobbled this together so it could be totally
wrong.

Dim ws As Worksheet
Dim DataName As String
Dim Formula As String

On Error Resume Next

Set ws = ActiveSheet
Formula = "=Offset(" & ws.Name & "!R1C1, 0, 0, CountA(" & ws.Name &
"!C1), CountA(" & ws.Name & "!R1))"

DataName = Application.InputBox("What do you want to call this range of
Data? NOTE: Assumes that your database starts in cell A1 of the
active worksheet.", "Name your data", "Database" & ActiveWorkbook.Names.Count
+ 1)
ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:=Formula
End Sub

 

[Bernie Deitrick]

Jonathan,

Use single quote marks around the ws name:

Formula = "=Offset('" & ws.Name & "'!R1C1, 0, 0, CountA('" & ws.Name &
"'!C1), CountA('" & ws.Name & "'!R1))"

HTH,
Bernie
MS Excel MVP

 

[Jonathan Cooper]

Will that work for both ways? When there is no space, and when there is?

 

[Jonathan Cooper]

I answered my own question. IT WORKS! Thank you.