Setting up dynamic named ranges in code

I normally set up my dynamic named ranges in the name manager using formulae like

Please Login or Register  to view this content.

I now need to be able to set up these ranges using vba code and have come up with the problem that "Offset" in vba has a different meaning from "Offset" in the DefineName window.

I could, I suppose, erase the names after every use and redefine them afresh each time I need them, but this seems cumbersome, the ranges will change from time to time and I would like to keep their dynamic nature.
What is the best way to deal with this in code.
John

Originally Posted by j_Southern

the ranges will change from time to time and I would like to keep their dynamic nature.

In what way do they change ?
Perhaps the original formula can be modified to account for that 'time to time adjustment'

JonMo1, Thanks for your interest, The ranges will only ever change by increasing the number of rows.
ChemistB, Here is how I have used your code in my sub routine.

Please Login or Register  to view this content.

This does create new ranges with the right names. The RefersTo column in the NamesManager however reads
=Offset(ws.Name!$A$8,0,0,Counta(ws.Name!$A:$A)-2,41)

It looks as though ws.name has been treated like a string rather than a variable. I am going to try:-

Please Login or Register  to view this content.

And that worked! Thanks for your help
John

Originally Posted by j_Southern

The ranges will only ever change by increasing the number of rows.

Isn't that the purpose of using COUNTA($A:$A) in your original formula?

JonMo1
Yes I did use counta for that purpose. My original formula was entered manually into the Define Name window. I then needed to modify the original application and I took the opportunity to automate the setting up of a new year. This involved deleting sheets and then creating new ones for the new year. I then had to delete the range names (they are not deleted when the sheet they occupy is deleted). Then I had to reinstate the range names using VBA and this is where I got stuck. All is OK now though as ChemistB's idea, modified so as to use a variable for the sheet Name, worked fine.
John