Names
Posted by Lukas Weder on January 10, 2002 7:36 AM
I defined in 10 different files "names" for a datarange(Insert|Names|Define...). In all the files is exactly the same range (A1:A10). When I want to modify the the range(ex. A1:A11), I have to do it ten times, for each file once. Is there a way to modify them all at once?
Thank you again!
Lukas
Posted by Dan on January 10, 2002 7:50 AM
You could probably do it pretty easily in a macro, but why would you have 10 named ranges, all referring to the same range?
Posted by sandra on January 10, 2002 8:01 AM
Hi Lukas
1. Open all files you want to change
2. Write the following macro in Visual Basic Editor
For Each w In Workbooks
w.Names.Add Name:="names", RefersToR1C1:= _
"=Sheet1!R1C1:R11C1"
Next w
Run this macro
PS: change Sheet1 to the real name of the sheet
Posted by Lukas Weder on January 10, 2002 8:08 AM
sorry, I didn't described the situation very clearly. The 10 named ranges don't refer to the same range... I have in file1.xls a named range B10:X10 and in file2.xls a named range B10:X10 and in file3.xls ... The names of this ranges is "Months". So time by time I add a month and I want that the names for the ranges are now B10:X11 so that the new month is included in the named range...
Lukas
Posted by sandra on January 10, 2002 8:27 AM
Lukas,
you can try this (you have to open all files first)
Then write the following macro in VB editor (F11)
Sub ChangeName()
For Each w In Workbooks
w.Names.Add Name:="Months", RefersToR1C1:= _
"=Sheet1!R10C2:R11C24"
Next w
End Sub
Run this macro
Posted by Lukas Weder on January 10, 2002 8:40 AM
THANK YOU!!! It's exactly what I needed!!!
