Unable to make the name Property of the Range Object Local

[MichaelDavid]

Greetings! On page 128 of "Excel 2007 VBA Programmer's Reference" it says
with respect to creating a name which refers to a range: "If you want the
name to be local, you can include a worksheet name: Range("F1:F10").Name =
"Sheet1!Staff" "
In my workbook, BookIssuers.xlsm I have a number of worksheets. The Active
Worksheet is "24Iteration" but it could be any of the worksheets in the
workbook. I tried to make a range on the Active Worksheet local as follows:

First I declared SheetName As String:

Dim SheetName As String

Then, I retrieved the Sheet Name and displayed it as follows:
SheetName = ActiveSheet.Name
MsgBox SheetName
I also tried:
MsgBox ActiveSheet.Name

Both of these MsgBoxes indeed displayed "20Iteration" as expected.

I then tried to name the local range IssuerLabels as follows:

Range("C3:C5").Name = "SheetName!IssuerLabels"

When I run the above instruction, I get:

"Run-time error '1004':
Application-defined or object-defined error"

I then tried running:
Range("C3:C5").Name = "ActiveSheet.Name!IssuerLabels" and got the same error
message.

I then substituted in the actual name of the sheet and ran the instruction:
Range("C3:C5").Name = "24Iteration!IssuerLabels"

which runs without error. Any help or suggestions will be greatly appreciated.

--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[OssieMac]

Hi Michael,

The reason is that where you use VBA variables in place of real strings. You
do not include the variable between the quotes but concatenate it with the
part between quotes using the ambersand.

Range("C3:C5").Name = Sheetname & "!IssuerLabels"

Note that ActiveSheet.Name is really a variable.
Range("C3:C5").Name = ActiveSheet.Name & "!IssuerLabels"

The above can be confusing where you are actually using the defined names in
a range in lieu of the actual address. Named ranges are not VBA variables but
belong to the worksheet and are saved with the worksheet and they are
included between the quotes just the same as the cell address as per the
following example.

Range("C3:C5") becomes Range("IssuerLabels")

 

[MichaelDavid]

Hi OssieMac! That solved ALL my problems! You really aced that one. I
THOROUGHLY checked out your solution, and my macros ran perfectly. If I were
your boss, I'd give you a HUGH raise.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[MichaelDavid]

Greetings! One last problem: The sheet name sometimes has blanks in it. I
tried the following instruction to eliminate the blanks from the sheet name:
ActiveSheet.Name = Trim(ActiveSheet.Name)
When this instruction is executed, there is no diagnostic, but this
instruction does not remove the blanks. Hopefully this problem can be as
easily resolved.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[MichaelDavid]

Greetings! I mis-spoke. The Trim just elimianates leading and trailing
blanks. I should be using the Replace Function to eliminate ALL blanks.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[OssieMac]

Hi again Michael,

I haven't been in all day so just got your message. If the sheet name has a
space in the name then you need to concatenate single quotes around the name
like the following 2 examples.

The following code uses the ascii code of a single quote to concatenate the
single quotes around the sheet name. Assume sheet name is something like My
Sheet with a space between the words.

sheetname = ActiveSheet.Name
Range("C3:C5").Name = Chr(39) & sheetname & Chr(39) & "!IssuerLabels"

The next line uses the single quote enclosed in double quotes to concatenate
the single quotes around the sheet name.

Range("C3:C5").Name = "'" & sheetname & "'" & "!IssuerLabels"

Both methods are basically the same. It is just a matter of preference which
method you use.

 

[MichaelDavid]

Greetings OssieMac:
Thanks immensely for your help. I will be putting your latest help to use
shortly.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick