defined name to multiple sheets?

jjst34 · Jan 10th 2005

Hi there,
I am trying to give certain cells in EVERY sheet the same name. When I originally set this up, it worked out fine. Now that I am trying to add new names to each sheet, it seems to only want to save to the active sheet. I tried a 'select all' sheets and then go to 'insert - name - define', but it only saves to one sheet.. I am wondering if anyone knows how to save the same name to say, cell A1, in every sheet. I have attached a screen shot of my define names box, and you'll see the name jw21.5 isn't saved quite the same. Thanks for any help!

shades · Jan 10th 2005

Re: defined name to multiple sheets?

Names can be either sheet-specific or workbook; can be confusing. One thing that might help you is to get the Name Manager, which allows you to globalize names.

http://www.decisionmodels.com/downloads.htm

shades · Jan 10th 2005

Re: defined name to multiple sheets?

You shouldn't use a period in the name.

jjst34 · Jan 10th 2005

Re: defined name to multiple sheets?

thanks I'll give name manager a shot!

shades · Jan 10th 2005

Re: defined name to multiple sheets?

Maybe a little more info on what you are trying to do and why you are using names this way. There may be a better way.

jjst34 · Jan 10th 2005

Re: defined name to multiple sheets?

I have vba enter a vlookup in a cell then paste it to about 200 different cells w/ no real pattern, so I figured the easiest way was to name them and reference the name putting the cell reference in vba. I'm sure there is an easier way,, feel free to give input.. but this does get the job done! LOL

Code

Windows("Bal. Sheet Summary 05.xls").Activate
    Range("jw10").Select
    ActiveCell.FormulaR1C1 = _
         "=IF(ISERROR(VLOOKUP(RC14,bs.xls!R2C2:R2000C5,4,FALSE))=TRUE,0,VLOOKUP(RC14,bs.xls!R2C2:R2000C5,4,FALSE))"
    
    
    Range("jw10").Select
    Selection.Copy
    Range("jw11,jw12,jw13,jw14,jw15,jw16,jw17,jw18,jw19,jw20,jw21,jw21.5,jw22,jw23,jw24,jw25,jw26,jw27,jw28,jw29,jw30").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False




    Range("jw10").Select
    Selection.Copy
    Range("jw31,jw32,jw33,jw34,jw35,jw36,jw37,jw38,jw39,jw40,jw41,jw42,jw43,jw44,jw45,jw46,jw47,jw48,jw49,jw50").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False




    Range("jw10").Select
    Selection.Copy
    Range("jw51,jw52,jw53,jw53.5,jw54,jw55,jw56,jw57,jw58,jw59,jw60,jw61,jw62,jw63,jw64,jw65,jw66,jw67,jw68,jw69,jw70").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False




    Range("jw10").Select
    Selection.Copy
    Range("jw71,jw72,jw73,jw74,jw75,jw76,jw77,jw78,jw79,jw80,jw81,jw82,jw83,jw84,jw85,jw86,jw87,jw88,jw89,jw90").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False




    Range("jw10").Select
    Selection.Copy
    Range("jw91,jw92,jw93,jw94,jw95,jw96,jw97,jw98,jw99,jw9912,jw9913,jw9914,jw9914.5,jw9915,jw9916,jw9917,jw9918,jw9919").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False


    Range("jw10").Select
    Selection.Copy
    Range("jw9920,jw9921,jw9922,jw9923,jw9924,jw9925,jw9926,jw9926.5,jw9927,jw9928,jw9929,jw9930,jw9931,jw9932,jw9933").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False






    Range("jw10").Select
    Selection.Copy
    Range("jw9934,jw9935,jw9936,jw9937,jw9938,jw9939,jw9940,jw9941,jw9942,jw9943,jw9944,jw9945,jw9946,jw9947,jw9948").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False




Range("total_variance").Select
Call Remove_functions_from_BS

Display More

shades · Jan 10th 2005

Re: defined name to multiple sheets?

For those cells on the same page, you can name the group of cells, rather than each individual cell.

i.e. Name: jw10

=Sheet1!$B$2,Sheet1!$C$30,Sheet1!$D$37

jjst34 · Jan 10th 2005

Re: defined name to multiple sheets?

the only reason i didn't is because sometimes I have to insert rows etc.. and i didn't want to have to change the code all the time.

shades · Jan 10th 2005

Re: defined name to multiple sheets?

I would probably add an Admin sheet, with columns, each column would have a list of defined names and some type of reference to the cells in that named range. Then you would only need to change the Admin sheet and not the code.

jjst34 · Jan 10th 2005

Re: defined name to multiple sheets?

do you have an example of this? Sounds good though

shades · Jan 10th 2005

Re: defined name to multiple sheets?

Quote from jjst34

Hi there,
I am wondering if anyone knows how to save the same name to say, cell A1, in every sheet.

Yes, in the INSERT > Name dialog box, give the name Test and in the Refers to box put this:

=Sheet1:Sheet10!$A$1

This will work for any sheet placed between the two sheets. Keep in mind that some formulas work well with this 3D name, and others do not.

mje · Jan 27th 2005

Re: defined name to multiple sheets?

I'm having the same problem. I've spent half the day trying to figure this out, with no luck. I've got a WB with 13 sheets, 2 thru 13 are named Jan thru Dec. I want $AA$281 to be named "tmo" sheets 2 thru 13. In sheet1, named Monthly Avg, I should be able to sum all sheets with Jan!tmo+Feb!tmo, etc.

This worked great in Dec when I set it up. There are about a dozen named cells, but I inserted new rows and need a new name and it won't work. The other named cells still work fine.

TIA...MJ

jjst34 · Jan 27th 2005

Re: defined name to multiple sheets?

what worked for me was 2 things.. Name them all in one sheet.. get rid of the rest (if you can), then copy the 1 w/ the names in it,, and they copied over..
Or..
You can use that "name manager" shades recommened.. Which sorta worked.. it got the named range in all the sheets.. but when I went on that cell it wasn't there,, however when I went to each sheet afterwards and named it, it stayed in all sheets once saved.....

mje · Jan 28th 2005

Re: defined name to multiple sheets?

That did it. It will be a pain getting cols back in for days of the month, but less time than I spent yesterday trying to figure out the problem!! Thinking back on this, the wb was created from the first monthly sheet and then copied.

Thanks for your help...greatly appreciated!!

MJ

jjst34 · Jan 28th 2005

Re: defined name to multiple sheets?

no problem! glad to help!!

defined name to multiple sheets?

Participate now!

Share

Users Viewing This Thread