Excel 2003 - VBA - Creating Names

C

C Brandt

I have a problem creating a name using VBA. The problem is the generation of
the WorkSheet Name.
The line of code that generates the name is:
ActiveWorkbook.Names.Add Name:=IndName, RefersToR1C1:="=" & WSName &
"!R2C1:R" & MaxRng & "C120"

When viewed with the "Watch" feature in VBA, the WSName is equal to "12-07"
at the time of execution.
When viewing the results in Excel under the menu item Insert/Name/Define
and the Name selected, the Name "Refers to:"

=12-'07'!$A$2:$DP$455
it should be :
='12-07'!$A$2:$DP$455

Is there a way to get the ' in the right location?

Thanks,

Craig
 
D

Dave Peterson

I'd use:

dim wks as worksheet
set wks = worksheets("12-07")
with wks
'a worksheet level name
.range("A2",.cells(maxrng,120)).name = "'" & .name & "'!" & indName
'or as a workbook level name
.range("A2",.cells(maxrng,120)).name = indname
end with

But you could try:

ActiveWorkbook.Names.Add Name:=IndName, _
RefersToR1C1:="='" & WSName & "'!R2C1:R" & MaxRng & "C120"
 
C

C Brandt

Dave:

I tried the second suggestion and it didn't allow me to continue, but the
first worked like a champ.
I now will have to sit down and study it awhile until I understand why.

Many thanks,

Craig
 
D

Dave Peterson

All three suggestions worked for me.

Maybe it was something to do with wsname held--or what maxrng held???
 
Top