Dynamic named range - problem when sheet's name renames!

[jakuza]

I create a dynamic named range for certain cells in one of my sheets. The problem is when user changes the name of that sheet. So, I decide to make VBA code which will run when sheet changes, but I can not make it done right.

My dynamic named range is: MyBooks
and its formula is: =OFFSET('Book list'!$A$1;0;0;COUNTA('Book list'!$A$1:$A$500);1)

I also made this code:

Dim BooksName As String

    For Each WS In Worksheets
        If WS.CodeName = "Books" Then
            BooksName = WS.Name
            Exit For
        End If
    Next WS

    ActiveWorkbook.Names.Add Name:="MyBooks", RefersTo:= _
    "=OFFSET('" & BooksName & "'!$A$1;0;0;COUNTA('" & BooksName & "'!$A$1:$A$500);1)"

This code gives error 1004. What is wrong with above code?

I run the code through Step Into and create Add watch and find that the formula in code have exactly the same string value as my original formula but it is still not working.

Any help?

 

[iyyi]

Changing the Sheet name by the user should change the name definition dynamically. You better check that first.

 

[jakuza]

Yes iyyi,
you are right! I did not test it, my mistake. It is changed dynamically, which is great!

But, what is wrong with code, how to create new named range using VBA for instance?

 

[Mytå]

Hi

Replace the semicolons with commas.

    ActiveWorkbook.Names.Add Name:="MyBooks", RefersTo:= _
     "=OFFSET('" & BooksName & "'!$A$1,0,0,COUNTA('" & BooksName & "'!$A$1:$A$500),1)"

NMytå

 

[jakuza]

No way!!
I thought I tried this... I read hundreds of time that this is occasionally problem with excel-semicolons and commas.
The strange is that it is comas in VBA, but when I edit range in excel it is semicolons, but it is working that is only important.

Thanks a lot NMytå and you iyyi also