Naming ranges

  • Hi all,


    I wonder if anybody can help me with this?


    I'm trying to name a range by VBA so that the name of the range corresponds to the name of the worksheet does anybody have any idea how to do this?


    Thanx


    Robert

    .¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

  • Re: Naming ranges


    Right, sorry for being a bit fuzzy!


    What I have is a file that is generated everyday with new figures and this new file is moved to Excel from Access. What I do then is to sum a column in a cell whose address changes due to the amount of rows in the file, this cell where the sum is, is what I would like to have the same name as the worksheet. Say if for example the sheetname is 2006-01-18 then the cell where the sum is should also have the range name 2006-01-18.


    Robert

    .¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

  • Re: Naming ranges


    :thanx: Badger!


    But as you believed there are some naming issues. A range can not have the same format as a date, any ideas how to get round this? I name the sheet by looking at a field that always has the same address but updates with regards to the date.


    Cheers


    Robert

    .¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

  • Re: Naming ranges


    Or change the name of the sheet to be preceeded by a letter possibly or change the - to _.

  • Re: Naming ranges


    it will still fail on changing - to _ as it still commences with a numeric. And adding a letter will fail due to the -. doing both would work.


    If you are pulling the sheet name from a file field then your probably want to maintain the sheet name as it. I would still suggest rethinking why you want to name the range as the sheet name. There will be easy ways around this.

  • Re: Naming ranges


    Ok let me explain a bit more.


    The workbook contains one month of worksheets ie 30 or 31 sheets per month. The summed amount in every sheet should be represented in a summary worksheet with the date and the result of the summed amount for that date. So what I am trying to do is to create a VBA that will post the date and the summed amount in this summary sheet. The problem is that the file is dynamic in the amount of rows per day so the cell address will unlikely to be the same the next day which leaves out using a simple reference formula. I might be going about this in an erroneous fashion but my VBA skills are limited so I am learning by doing....


    Robert

    .¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

  • Re: Naming ranges


    Can you reformat your summary sheet to put the sumation at the top rather than the bottom?


    If not, there is VBA code to find the last used row like this:


    Range("A65536").end(xlUp).row

  • Re: Naming ranges


    So... I think what I'm hearing is that for each sheet you want a named range; perhaps we'll call it "MyRange". And you have maybe a macro that always operates on "MyRange" but you maybe want "MyRange" to represent a different sized range for each sheet. Is that it?


    I've done stuff like that before. If you want to create a "Sheet Specific" named range, you have to assign the name and use the sheet name as a prequalifier. Like so:


    Code
    ActiveWorkbook.Names.Add Name:="Sheet1!MyRange", RefersTo:="=Sheet1!A1"


    However, I find that this type of naming is required only in the rarest of occasions. I would think very carefully before proceeding down that path... I guess if you have the same formula (maybe a lookup) that keys on MyRange it might be useful to have that be a different size for each sheet.


    Edit: But remember, the definition of MyRange on the sheet that you enter the lookup formula on is going to be the only definition of MyRange that can be seen by formulas on that sheet. You won't be able to have a formula on a sheet and have MyRange mean different things to different formulas on the same sheet. Am I making sense? Multiple instances of the same name can also be confusing and misunderstood by those trying to audit your work.


    Also worth noting that it's certainly not difficult to add sheet specific named ranges manually. Just need that sheetname prequalifier in the name.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Naming ranges



    Well yes and no, what I am trying to do is to use the cell where the sum is and transport it to a seperate sheet by using the named range. The thing is that the file has no other useful identifier than the date that I can use for the summed cell. I know that this is not a lot of work to manually change the name of a range but I have about 100 workbooks where I have to repeat the procedure so I am trying my hardest to do as little work as possible ;o)


    Cheers


    Robert

    .¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

  • Re: Naming ranges


    I 'think' I'm starting to follow what you're after...


    Have you thought about maybe having a simple formula in each workbook that displays the count of numbers in the column? Perhaps then you could use that as a variable along with an OFFSET to pull the sum maybe to a static cell reference and then use the static reference outside the workbook for the summary?


    Of course, if you're inserting rows to build the data in each daily workbook, the OFFSET wouldn't be required, just a simple reference formula to the sum of the values would be sufficient.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Naming ranges


    Thank you all for your help, I have taken the same approach with the same range name as sheet name but I manipulate the data from Access so that it delivers the date field as text ie 20060120 works like a charm.


    Again thank you all!


    :thanx:
    Robert

    .¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!