Hi Rachael,

I have also tried
ActiveWorkBook.Names.Add Name:strRangeName,
RefersTo:=strRefersTo

No matter what I try I can not define named ranges


Make that:

ActiveWorkBook.Names.Add Name:strRangeName,
RefersToR1C1:=Activeworkbook.Activesheet.Range(str StartOfRange & ":" &
sreEndOfRange)

Note that by defining these names the "old" names (to the other sheets)
will be overwritten.

NB: Download my name manager from my website!

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Code seems OK except that we have to add the "=" to RefersTo, therefor
(partial) :-

RefersToR1C1:="=" & Application.Activesheet.Range(strStartOfRange & ":
& sreEndOfRange)

Don't need ActiveWorkbook because ActiveSheet is at application leve
too. Cannot check further without seeing your data. You could put
line in to check :-

Msgbox(ActiveSheet.Range(strRangeName).Address

--
Message posted from http://www.ExcelForum.com

Hi BrianB,

Code seems OK except that we have to add the "=" to RefersTo, therefore
(partial) :-


You are right, I made a mistake.

This should do it:

Activesheet.Range(strStartOfRange & ":" &
sreEndOfRange).Name=strRangeName

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com