Accessing a macro created named range

[John]

I am using the following code in a macro to set up a named range on a
worksheet for use by other macros. I create the named range with the
following code:

ThisWorkbook.Names.Add Name:="Table1", RefersTo:=NewSheetName & "!$B$9:$H$500"

The new named range appears fine in the Developer > Name Manager list, but
does not appear in the Name Box drop down list and I code such as
i = Range("_PAI_Table_bbb").Rows.Count
that uses Table1 errors w/ a 1004 range of object _Global failed.

Somehow I'm either not creating the range properly and/or I'm not
referencing it correctly.

I appreciate your help, -John

 

[OssieMac]

Hi again John,

I had another look to see exactly what was occurring. When naming the range
with your code if you go to name manager and select the name then in the
Refers to field you will see double quotes around the sheet name and range
like this.

="MyTestSheet!$B$9:$H$500"

If you create the named range manually then you will see that the double
quotes are not there like this.

=MyTestSheet!$B$9:$H$500

The code that I have given you will create the named range correctly without
the double quotes.

I have no idea why xl does not error out with your creation code. Just one
of those thing I suppose.

 

[OssieMac]

Hi John,

Syntax for naming the range is incorrect. It does appear to name the range
but not properly and it appears that xl does not error out on it. Try this.

Dim newSheetName As String
Dim i As Long

newSheetName = "MyTestSheet"

ActiveWorkbook.Names.Add Name:="Table1", _
RefersTo:=Sheets(newSheetName).Range("$B$9:$H$500")

i = Range("Table1").Rows.Count

 

[Peter T]

Probably the variable NewSheetName does not refer to the string name of a
worksheet in ThisWorkbook.

Regards,
Peter T

 

[OssieMac]

Hi Peter and John,

Just for interest, the way John had coded the sheetname and range is missing
the leading equal (=) sign in the concatenated string.

VBA has then placed double quotes around the entire string which made the
reference invalid.

The method I posted does not need concatenation and I think is easier to
code. However, if coding with John's method then it should be like this.

ThisWorkbook.Names.Add Name:="Table1", RefersTo:= _
"=" & newSheetName & "!$B$9:$H$500"

 

[Peter T]

Yes you are right. At best, my suggestion was incomplete.

Regards,
Peter T

 

[Dave Peterson]

I find that this syntax is easier to use:

worksheets(newsheetname).range("b9:h500").name = "Table1"
(for a workbook name)

or

with worksheets(newsheetname)
.range("b9:h500").name = "'" & .name & "'!Table1"
end with
(for a worksheet level name)

 

[John]

Ossie and Peter, thx so much! This worked great! I really appreciate your
help, John