Batch Naming of ranges

Dear all,

    There are 80 sheets in my workbook. I want to name the ranges A1:B20 
with systematic names in these 80 sheets, say, student_01 for Sheet1!A1:B20, 
student_02 for Sheet2!A1:B20, ..., student_80 for Sheet80!A1:B20. Can I name 
these ranges easily? Thanks.

Best Regards,
Andy 


0
12/24/2005 9:34:38 AM
excel 39879 articles. 2 followers. Follow

4 Replies
540 Views

Similar Articles

[PageSpeed] 31

Dim i As Long
    For i = 1 To 80
        Worksheets("Sheet" & i).Range("A1:B20").Name = "student_" &
Format(i, "00")
    Next i


-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Andy Chan" <chankhandy-msnewsgroup@yahoo.com.hk> wrote in message
news:43ad162e$1@127.0.0.1...
> Dear all,
>
>     There are 80 sheets in my workbook. I want to name the ranges A1:B20
> with systematic names in these 80 sheets, say, student_01 for
Sheet1!A1:B20,
> student_02 for Sheet2!A1:B20, ..., student_80 for Sheet80!A1:B20. Can I
name
> these ranges easily? Thanks.
>
> Best Regards,
> Andy
>
>


0
bob.phillips1 (6510)
12/24/2005 11:02:13 AM
Hi Andy,

Sub InsertNamedSheets()
Dim i As Long
For i = 1 To Worksheets.Count
    ActiveWorkbook.Names.Add Name:="Student_" & Format(i, "00"), 
RefersToR1C1:= _
        "=Sheet" & i & "!R1C1:R20C1"
Next i
End Sub

If you're new to macros, look here first:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

-- 
Kind regards,

Niek Otten

"Andy Chan" <chankhandy-msnewsgroup@yahoo.com.hk> wrote in message 
news:43ad162e$1@127.0.0.1...
> Dear all,
>
>    There are 80 sheets in my workbook. I want to name the ranges A1:B20 
> with systematic names in these 80 sheets, say, student_01 for 
> Sheet1!A1:B20, student_02 for Sheet2!A1:B20, ..., student_80 for 
> Sheet80!A1:B20. Can I name these ranges easily? Thanks.
>
> Best Regards,
> Andy
> 


0
nicolaus (2022)
12/24/2005 11:13:48 AM
Thanks, it works!

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> ���g��l��s�D:eRG3TmHCGHA.3496@TK2MSFTNGP11.phx.gbl...
> Dim i As Long
>    For i = 1 To 80
>        Worksheets("Sheet" & i).Range("A1:B20").Name = "student_" &
> Format(i, "00")
>    Next i
>
>
> -- 
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Andy Chan" <chankhandy-msnewsgroup@yahoo.com.hk> wrote in message
> news:43ad162e$1@127.0.0.1...
>> Dear all,
>>
>>     There are 80 sheets in my workbook. I want to name the ranges A1:B20
>> with systematic names in these 80 sheets, say, student_01 for
> Sheet1!A1:B20,
>> student_02 for Sheet2!A1:B20, ..., student_80 for Sheet80!A1:B20. Can I
> name
>> these ranges easily? Thanks.
>>
>> Best Regards,
>> Andy
>>
>>
>
> 


0
12/24/2005 11:24:33 AM
Thanks, it works!

"Niek Otten" <nicolaus@xs4all.nl> ���g��l��s�D:eb2DZsHCGHA.3984@TK2MSFTNGP14.phx.gbl...
> Hi Andy,
>
> Sub InsertNamedSheets()
> Dim i As Long
> For i = 1 To Worksheets.Count
>    ActiveWorkbook.Names.Add Name:="Student_" & Format(i, "00"), 
> RefersToR1C1:= _
>        "=Sheet" & i & "!R1C1:R20C1"
> Next i
> End Sub
>
> If you're new to macros, look here first:
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> -- 
> Kind regards,
>
> Niek Otten
>
> "Andy Chan" <chankhandy-msnewsgroup@yahoo.com.hk> wrote in message 
> news:43ad162e$1@127.0.0.1...
>> Dear all,
>>
>>    There are 80 sheets in my workbook. I want to name the ranges A1:B20 
>> with systematic names in these 80 sheets, say, student_01 for 
>> Sheet1!A1:B20, student_02 for Sheet2!A1:B20, ..., student_80 for 
>> Sheet80!A1:B20. Can I name these ranges easily? Thanks.
>>
>> Best Regards,
>> Andy
>>
>
> 


0
12/24/2005 11:24:37 AM
Reply:

Similar Artilces:

Formula to capture worksheet names #2
this is Exactly what I need. I appreciate ya bro -- LowKe ----------------------------------------------------------------------- LowKey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1527 View this thread: http://www.excelforum.com/showthread.php?threadid=26897 No problem. Glad it helped. :) Take care -- firefyt ----------------------------------------------------------------------- firefytr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=664 View this thread: http://www.excelforum.com/showthread.php?threadid=26897 ...

Exchange 2000
Hi all I've recently renamed a user. Now I found that everytime I type that user name in the outlook, it shows me the name like this NewUserName <OldUserName>. I've checked all in the AD, but not found anythings related to the old user. How to correct it? Please help. Thanks BT Just a few points ... 1 - you can delete the names from the autocomplete by highlighting them (use the keyboards arrow keys) and hitting the DEL key. 2 - the <OldUserName> is displayed because that is the user's LegacyDN value. This value can be changed by using adsiedit however if...

why are my excel file name colored blue ?
Why are some of my excel files colored blue and some are in black? This occurred after an IT tech servicing was done> "valbwoy" <valbwoy@discussions.microsoft.com> wrote in message news:5A643AA1-3971-4498-A24D-2C569D330225@microsoft.com... > Why are some of my excel files colored blue and some are in black? This > occurred after an IT tech servicing was done> If this is when you are viewing the files in Windows Explorer, the blue may well mean that the relevant files are compressed. There is an option in Control Panel that allows compressed files to be shown ...

Printing Multiple Ranges
I have a worksheet that has 16 different ranges (entitled Page1, Page2, ..., Page16). I want to print each page in its correct order. So I go to Page Setup and make the print area be "Page1, Page2, Page3" and so on. But instead of keeping the ranges in that order, it converts them to cells so the print_area becomes J139:ER193. This wouldn't be a problem, except that the pages aren't in the correct order in the worksheet, so when I print I get page 6, 7, 8, 13, 1.... So I ask, is there a simple way to do this in excel? I also tried to write a macro to do this, a...

List output to a x by 10 range
I have a list that x lines long. I want to transpose that list to another range (starting on cell M1) that is 10 columns wide and however many rows long. I know this is super simple. Does it have something to do with range resizing? No doubt you've already experimented with macro recorder, discovering the standard transpose command wont wrap within a range. So we need handle the transposition manually. Presuming your data begins in cell A2: Sub test() For Each c In Range("A2", "A" & Range("A65536").End(xlUp).Row) Row = Row + 1 ...

Need trailing zeroes to show for range of numbers....
The file I pulled into Excel for some reason cut off 2 trailing zeroes, no matter how I format it they will not show up. There are thousands of them and I can't see typing in each one over just to get two zeroes on the end. Does anyone know of a way to format this so they will show up? I have searched Microsoft online with no help. Not too much information, but try this (after saving your workbook!) In an empty cell enter the numner 100. Edit>Copy Select your data Edit>Paste Special, check Multiply -- Kind Regards, Niek Otten Microsoft MVP - Excel "Sueshe" &...

count the number of new lows within a range of cells
Hello, Is there a formula or method to count the number of new lows within a range of cells? For example, the following are in col A rows 5 to 14. Within this set a new low has been reached 3 times- (96,95,94). What I would like is a formula or macro that that would produce the results - 3. There are about 9000 rows of data and I am looking for "count of new lows within the preceding 10 rows" on a rolling basis. Thanks. 100 101 96 97 98 97 95 97 94 95 Version 1: Use this if the 9000 or so rows don't have blanks in them. In this version you just need to select the first cel...

Sort name from specified interval table
Hiiii I am facing a silly problem i think u guys are rescue me from that... My prob like I have two cloum one colum contain Name of person and another coloum contain their salary.I prepared a interval table contain 4 row according to salary range....I have find the name whos salary has on the specified range on ist row of interval table.... Can any one giv the idea about that..Perhaps I tried it VLook up Function...But i faced proble to sort the name from ist colum -- mun04 ------------------------------------------------------------------------ mun04's Profile: http://www.excelforum....

Defining a name
Whats up everyone. Can I have a table array that is dynamic based on a input sheet. For example I have a lookup table that =VLOOKUP(Reference!$E6,CompanyDataQ3,Reference!$F6,FALSE) I would like CompanyDataQ3 to change based on the input sheet. On the input sheet I have Quarter so if quarter on the input sheet is Q3 the lookup table will have =VLOOKUP(Reference!$E6,CompanyDataQ3,Reference!$F6,FALSE) if the quarter on the input sheet is Q4 than the lookup table will hav =VLOOKUP(Reference!$E6,CompanyDataQ4,Reference!$F6,FALSE) Please let me know if this is possible and how to best a...

in access databse the function date() returns #name
i have created an access data base at access 2003 profisonal and used the function date() it worked properly . but when i have coped my data base at another computer at the same office , the funtion retuned (name#) aW4gQWNjZXNzOyB0aGUgRGF0ZSBmdW5jdGlvbiBkb2Vzbid0IHdvcmsgc29tZXRpbWVzLgoKaXQgd29ya3MgYWxsIHRoZSB0aW1lIGluIFNRTCBTZXJ2ZXI7IGJ1dCB5b3UndmUgZ290IHRvIGNhbGwgaXQKR0VUREFURSgpCgoKCk9uIE1hciA3LCAxMToyNMKgcG0sINmF2K3ZhdivINiq2KfYrCDYp9mE2KPYtdmB2YrYp9ihINin2YTYqNiu2YrYqiDZhdit2YXYryDYp9mE2LPZitivCjxAZGlzY3Vzc2lvbnMubWljcm9zb2Z0LmNvbT4gd3JvdGU6Cj4gaSBoYXZlIGNyZWF0ZWQgYW4gYWNjZXNzIGRhdGEgYmFzZ...

Outlook 2007, Cached Mode, name resolution fails
I'm having a similar problem as discussed here: http://groups.google.com/group/microsoft.public.outlook/browse_thread/thread/1f521281a6a07fa6/01bdb551c91c034d?lnk=gst&q=cached+mode&rnum=16#01bdb551c91c034d With Outlook 2007 the key for Cached Mode no longer exists. I'm also receiving the following error when I do a check names when I type portions of users display names: "Too many names have been found in the directory service or the server has exceeded its time limit for searching. Type more letteres of the name and try again." I've checked and our OAL is bei...

Shared Calendars not Displaying User Name
Outlook 2003 / Exchange 2003, both fully patched and running on XP Pro SP2. Shared calendars added under "Open a shared calendar". Header of calendars only displays "Calendar", not the username of the shared calendar owner. How can I get Outlook to display the username, please. At the moment I have multiple calendars open and they all are headed "Calendar". Not very useful :-( TIA, Bill http://www.outlook-tips.net/archives/2005/20050809.htm -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for W...

Possible to chart data for dates implicit within a range?
I have a list of computer programs with start and end dates for each. I need to determine how many programs were running on my system on any given day. Can I get this data for individual days between the start and end dates without creating columns for each intermediate day? Ex.: Program 1 ran from 01/01/2005 to 01/14/2005. Program 2 ran from 01/06/2005 to 01/22/2005. Is it possible to chart the total number of programs running on 01/08/2005 without manually creating a column for that date? Thanks, Nechama You can compute this with formulas. Star with data in A1:C4 as follows: ...

Automatic formatting of minimum/maximum value in a range.
I want that if I select a range of numbers the minimum or maximum should be selected by a single command How about cond. formatting? 1. Select the range. 2. Go to Format > Conditional Formatting 3. Select "Formula Is" and copy in this: =OR(INDIRECT("rc",0)=MAX(rng),INDIRECT("rc",0)=MIN(rng)) where "rng" is a defined name for your range. HTH Jason Atlanta, GA >-----Original Message----- >I want that if I select a range of numbers the minimum or maximum should be >selected by a single command >. > What do you want to do with t...

Mail Forwarding delays / batches of mail
There have been a couple of earlier threads on this topic, which concerned mail being forwarded to a Blackberry being subjected to delays and "batching" of multiple messages, as well as duplicates, but no indication that the problem was successfully addressed via the hotfix suggested (which I note has been subsequently superceded) My experience does not concern Blackberry forwards, but simply to external POP3 accounts to facilitate remote integration within Outlook. The situation is that mail sent to an Exchange 2003 mail account which has been set to forward to an external a...

Multiple Data files--two with same name!
I have been having trouble balancing my checking account, and discovered that I have three data files: My Money, My Money0 and My Money0. Yes, two of them appear to have the exact same name. One of the My Money0 files appears to cover the time from my last archive (Jan 2003), while the other two files cover only a portion of that time (Jan 04-Aug 04) and (Jan 03-May 04). How can this happen? Any suggestions on how to fix it? At this point my only idea is to manually cross check all the entries and create a single data file then delete the other two. Any better ideas? In micro...

Display name of mail enabled public folder
Hi, I have problems with the mail enabled public folder adresses. In the AD, there is "My Company" security group where there are employers of the company, without any mail address. We have Public Folders\Unsorted folder, mail enabled, with address company@company.com and company@company.local. If you send a message from Outlook 2007 to company@company.local it is delivered into the Unsorted folder as it should. However, if you send a message to company@company.com it is translated as "My Company" by outlook and rejected by server as undeliverable....

Linking to a data range containing blank cells
I'm using MS Excel 2003. I've got a chart from range of data gives the calculates accumulated total from another table and would like the option to have the calculation result in a blank cell that is not plotted as a zero. I tried the following IF statement: =IF(Q6="","",Q6+P12) but "" is still treated as a zero. Any suggestions? Thanks, - James =IF(Q6="",NA(),Q6+P12) -- David Biddulph "James from Sage" <JamesfromSage@discussions.microsoft.com> wrote in message news:8E4CCAF0-07B1-4398-95FE-605DDB6C6A44@microsoft.com......

Add-ins and #NAME errors
I have an Excel spreadsheet that was saved with a series of add-in function calls. The add-in wasn't loaded at the time, and the UDF cells contained #NAME errors. When I opened the sheet on a machine that did contain the relevant add-in, I expected that hitting F9, Shift+F9 or at the very most Ctrl+Alt+F9 would recalculate all the cells including the ones with the #NAMEs. However this was not the case. The only way I could fix this sheet was by using an F2+Enter combination on the broken #NAME cells. Since the range was relatively large, I used a VBA macro with SendKeys. ...

Segment names in FRx
We are a credit union audting deparment using GP to import data from credit unions and using this info for monitoring. We have set ourselves up as the company, and the credit unions as segments (instead of branches). I need to create a report which will list each credit union and some basic info (certain accounts). I can create this report so that each credit union is on a single page in a drilldown report, but how do I create it as such Name TotalAssets Acct 2 Acct 3 credit union 1 credit union 2 .... If I try to output this info to excel then I get all t...

excel function to get tab name
On the first tab in my worksheet, I have several cells in a colum in which the cell reference returns the value of cell A1 from the subsequent tabs. For example, the workbook has 10 tabs named Tab1, Tab2...Tab10, and in Tab1, the formula in cell A2 is "=Tab2!A1", and the formula in cell A3 is "=Tab3!A1"....cell A10 is "Tab10!A1". Sometimes, I will add and delete these subsequent tabs (Tab2 through Tab10) and then have to manually re-link the references to A1 on each of these tabs back to the column on Tab1. Is there some way to do this with a relative r...

Re: Sp[litting Names from Cells
Bob Phillips gave the answers below for splitting names from cells: =LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) and =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) Using these formulas on this example John A Doe results in John A and Doe, is it possible to split it to show John / A / Doe in 3 separate cells, I know I could use the formulas again on the John A result to split them but I'd like to do it in 1 go ...

How to set custom class name for MFC frame window?
When you create a frame window in MFC using following code: BOOL CMainFrame::PreCreateWindow(CREATESTRUCT& cs) { if( !CMDIFrameWnd::PreCreateWindow(cs) ) return FALSE; // TODO: Modify the Window class or styles here by modifying // the CREATESTRUCT cs cs.lpszClass = AfxRegisterWndClass(0); return TRUE; } In Spy++, the default frame name is Afx:00400000:0. How can I set custom name instead of the default name? Thanks. Michael On Wed, 16 Nov 2005 14:49:47 -0800, "Michael" <Michael@discussions.microsoft.com> wrote: >When you create a frame window in MFC usi...

how do you change the print merge batch size from 10 records?
How can I change the default output of 10 printed records to say 500? Chris Bartlett <ChrisBartlett@discussions.microsoft.com> was very recently heard to utter: > How can I change the default output of 10 printed records to say 500? http://support.microsoft.com/default.aspx?scid=kb;en-us;891904 has details of how to change the batch size to 1 for stapling/collating purposes. If you follow the instructions, but use a value of 500 instead of 1, then you will get 500 records per batch. -- Ed Bennett - MVP Microsoft Publisher ...

Universal Naming Convention
I am trying create a wed page in access 2003 to have viewed on the internet by another department. But it is telling me to use (UNC) instead of a drive letter, how can i give it a (UNC) If you're currently using, say, G:\Folder\File.mdb, find out what the drive mapping for G: is and use that instead: \\server\share\folder\file.mdb -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "juniorlee31" <juniorlee31@discussions.microsoft.com> wrote in message news:3D7B7547-D1B1-45A3-8B9E-108CD9142B8F@microsoft.com... >I am trying create a ...