Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range names in a Excell macros
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range names in a Excell macros
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 "karene" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range names in a Excell macros
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 "Michael Beckinsale" wrote: 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 "karene" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range names in a Excell macros
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 "karene" wrote in message ... 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 "Michael Beckinsale" wrote: 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 "karene" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range names in a Excell macros
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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range names in a Excell macros
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 "karene" wrote in message ... 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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range names in a Excell macros
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 ? "Michael Beckinsale" wrote: 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 "karene" wrote in message ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting email address names in a range of cells to real names | Excel Worksheet Functions | |||
version differences in excel 2007 - use of macros & range names | Excel Discussion (Misc queries) | |||
Excell Worksheet Tab Names | Excel Worksheet Functions | |||
COPYING FORMULA CONTAINING NAMES/RELATIVE RANGE NAMES | Excel Discussion (Misc queries) | |||
Excell Sheet Names | Excel Discussion (Misc queries) |