Naming Range automatically

[Yokiboha]

I am adding data to a sheet each month. On copying the months data I need to create a name for the new data which is the concatenated month and year, e.g. "July2011", which is the value in the first column of evrey row.

I would be grateful if anyone could tell me a way of doing this.

 

[capparfrank]

Are you using excel 2007 or 2010? If so, this can be accomplished VERY easily with the tools in the ribbon.

highlight all your data
go to 'Formulas' Tab
click the 'Create from Selection' Button in the 'Defined Names' Group

a dialog box will ask you what row/coloumn in your range should be used as the name for each range. In your case, it sounds like left column is the one.

Click OK and you're done!

Otherwise, you may need to write some VBA. The syntax basically looks like this:

Range("a1:a5").Name = "NamedRange1"

so, if you select the data on one row and want to use the leftmost cell as the name:

Selection.Name = Cells(Selection.Row, 1).Value

if you want to do a whole bunch of rows...

for ctr = 1 to whatever
range("b" & ctr & ":zz" & ctr).Name = range("A"& ctr).Value ' zz should be replaced with whatever is the rightmost cell in your dataset
next ctr

I didnt' really test that last chunk of code, but it's the general idea.

GL

 

[Yokiboha]

Many thanks for this, capparfrank.

The problem is that the number of rows copied will always vary, thus the range to be named will as well.

Sheet 1 contains an input area which is updated daily. At the end of each month the data is copied to sheet two and added below the previous months data, thus buliding up historical data.

Having copied the data to sheet two, I need to add to the macro to name each months data in sheet two with a concatenated name consiting of the month and year, e.g. "July 2011" This name is already on sheet one in cell D1, as well as in the first column of every row being copied.

 

[capparfrank]

Ok, so it sounds like a month’s data is going to be contained in more than one row and that there will be a different number of rows from month to month. However, each row will be prefaced with a value like ‘July2011’ which is a concatenated name consisting of the month and the year.


Maybe you need something like this? It reads each row (on sheet two) and compares the first column value to the prior row and determines which rows belong together (assuming they are sorted in contiguous blocks) and creates a named range to include all those rows.

I don't believe you may have spaces in the name of a named range -- I only mention this because of the space between 'July' and '2011' in your example above.

Sub createNamedRanges()
    Dim rangeStartingRow As Integer
    rangeStartingRow = 1
 
    Dim rowCtr As Integer
    rowCtr = rangeStartingRow + 1
 
    'a boolean flag for flow control.  We'll use this to make sure we don't try to add an already existing named range
    Dim nameExists As Boolean
    nameExists = False
 
    'run this procedure for every row that has data in column A, and break entirely when it encounters a blank
    While (Range("A" & rowCtr).Value <> "")
 
        'compare the value in the header column of the current row the value in the first row of this block of data.
        'If it isn't the same, we've reached the end of a month's data
        If (Range("A" & rangeStartingRow).Value <> Range("A" & rowCtr).Value) Then
 
            'iterate through the existing names.  If we find one that matches the name we would give to the current block of data,
            'turn on the "nameExists" flag
            nameExists = False
            For Each s In Application.Names
                If (s.Name = Range("A" & rangeStartingRow).Value) Then
                    nameExists = True
                    Exit For
                End If
            Next s
 
            'Create a named range for all rows in the prior block of data.  Skip this creation if the named range already exists
            If (Not nameExists) Then
                Range("A" & rangeStartingRow & ":ZZ" & rowCtr - 1).Name = Range("A" & rangeStartingRow).Value
            End If
 
            'update the "starting point" row to our current row.  We've finished processing the prior block of data
            rangeStartingRow = rowCtr
        End If
 
        'increment counter...
        rowCtr = rowCtr + 1
    Wend
 
    'We've ended the loop without writing the last named range (which is the very thing we were trying to accomplish)
    Range("A" & rangeStartingRow & ":ZZ" & rowCtr - 1).Name = Range("A" & rangeStartingRow).Value
End Sub

 

[Yokiboha]

Capparfrank, many thanks...it is exactly what I am looking for, and no there are no spaces between the month and year, that was my error.

 

[Yokiboha]

Capparfrank, many many thanks! Tried it last night and it works like a dream.