Range names in a Excell macros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I add a range name in an Excell macro to active workbook have several
with different name and record facilty keeps adding referes to xxxx in xxx
work book
 
Karene,

This is the code to add a dynamic range to a Named Range.

Dim source1
Sheets("List_Other").Select
Range("A2").Select
source1 = Range(Selection, Selection.End(xlDown)).Address
ActiveWorkbook.Names.Add Name:="Salutation", RefersTo:="=List_Other!" &
source1

If the range is not dynamic use the following code.
ActiveWorkbook.Names.Add Name:="MyRangeName", RefersTo:="=Sheet1!A1:A10"

Regards

Michael Beckinsale
 
Thanks I am new to macros as you can tell and it is a dynamic range that i
need to name but i get the message subscripts out of range and the debugger
is highlighting Sheets("List_Other").Select my problem is also that the tab
in each file i open also has a different name so i need the active sheet of
the active work book I belive ? do you have any other suggestions?

Thank you
 
Karene,

You need to replace "List_Other" with the name of worksheet that contains
the list. Obviously you will also have to change the address of the first
cell in the list as well. You must not have any blank cells within the list.

Regards

Michael Beckinsale
 
Sorry I think I must be being a bit dense here.
I have 95 differently named workbooks and each of them have their file name
as the sheet name like dec01 dec02 etc. I have run a set process on a
dynamic range on each of them. I have written the sub routine to do this and
said reference "data" and up until now i have opened each file and manually
set the range name to Data, and it was this bit i was trying to speed up, so
it looks like i would still have to open and rename something in each file to
be cosistant any way is that right ? Sorry for asking so many questions but
this woud be a great help if i could crack this porblem
 
Karene,

I assume that since you have 95 workbooks the workbook names are a
combination of

dec01, dec02, jan03, feb02, mar03 etc and that each respective workbook has
a sheet called dec01, dec02, jan03, feb02, mar03 etc

If my assumption is correct you would need to code the macro to loop thru
all the files, open them, assign the named ranges, save the workbook, close
the workbook.

If there are any other files in the directory you are using that you dont
want to "update" you need to find a unique hook for the files you do want to
update such as the length of the filename.

If you let me know if my assumptions are correct and what the unique hook is
l will send you the code to automatically loop thru all the workbooks and
assign the named range. It wont be until later today as l will be in a
meeting for a few hours.

Regards

Michael
 
Yes you are correct, they are in a directory with other files and each month
I set up a new directory, so the diretory for the current month is Dec05 and
all the files that need updatading with the range name data are XXdec05.xls
being 01Dec05.xls 02Dec05.xls etc I state the suffix becaues in this
directory there are also files of the same name with .TTX where they have
been exported from another porgram, would it be best to keep these in a
seperate directory ? So accordingly next month the directory and the files
will b eJan06 & XXJan06.xls

Thank you so much i hope you don't mind helping me out like this.
Just a thought do you run macro traning courses ?
 
Back
Top