Copying Named Ranges

foid025

I have a worksheet with a number of named cells (so that I can put data in them or retrieve data from them with VBA and not have to change anything should rows / columns be added or removed). I have a sort of template file from which I copy and paste the spreadsheet with all the named ranges to create a series of new files. The problem is, the cell names do not copy, so they do not appear in any of my new files. I also notice that on my original file, the range names are defined locally, like with ="Sheet1!$U$33". I imagine that if the sheet was to be renamed something other than Sheet1, I would have a problem.

So I guess my question is,

a) How can I name ranges in such a way that the name will stick even when I copy and paste the sheet to other workbooks.

b) How can I go about naming ranges relative to the active spreadsheet, such that calling Range("my Range name") will always work no matter what spreadsheet I am in, so long as there is such a named range in the worksheet.

Thanks!

MyLady

Hey,

Here is one you can play around with.

Sub CopyAllDefinedNames()

'Loop through all of the defined names in the active workbook and

'add each defined name from the active workbook to the target workbook ("Book2.xls").

For Each x In ActiveWorkbook.Names

Workbooks("Book2.xls").Names.add Name:=x.Name, RefersTo:=x.Value

Next x

End Sub

Best Regards

Cathrine

foid025

Great, thanks a lot Cathrine. I'll take a look at that and see if it works for me.