Using variable with ActiveWorkbook.Names.Add

Question

I'm trying to learn some new tricks with VB and needs some hints.

I am attempting to create formula names using several different variables to create these names.  All attempts have failed and I'm at a loss on where to look next.

Any suggestions on where to research would be appreciated.  It's one thing to supply the code and another thing to understand it for future uses.

Thanks for your time,

El Bee

My variables are:

    "Data " & yyyy  'This is the name of the worksheet   

    "Data_" & yyyy  'this is for creating a New Year database range   

    "Mth_" & yy & "_" & mm & "_amt"   ie.    Mth_16_01_amt  thru  Mth_16_12_amt

    For year 2015 these names and values are:

         Jan.    Mth_15_01_amt    ='Data 2015'!$C$8:$D$183

         Jun.    Mth_15_06_amt    ='Data 2015'!$C$8:$I$183

         Dec.    Mth_15_12_amt    ='Data 2015'!$C$8:$O$183

     The only thing that changes is the year, & month value.

         Jan.    Mth_16_01_amt    ='Data 2016'!$C$8:$D$183

         Jun.    Mth_16_06_amt    ='Data 2016'!$C$8:$I$183

         Dec.    Mth_16_12_amt    ='Data 2016'!$C$8:$O$183

First I attempt to create a database using the following syntax:

    Data_2016

        The range is a set number of rows and columns, this never changes.

        I.E.  for year 2015 it is   ='Data 2015'!$C$8:$Q$182   (taken from name manager)

        The only thing that changes is the Year value, the range remains the same.

            2015 becomes 2016 for the new year.   ='Data 2016'!$C$8:$Q$182

I've tried the Cstr() function but that didn't work; probably a syntax error on my part?

I recorded a macro in attemps to create these names using variables but that became a dead end.

Here's a few lines from the macro I created using the recording option.

Sub Macro1()

'

' created the new year 2016 database name and range.

' I want to replace the "2016" with 'yyyy'

    ActiveWorkbook.Names.Add Name:="Data_2016", RefersToR1C1:= _

        "='Data 2016'!R8C3:R182C17"

    ActiveWorkbook.Names("Data_2016").Comment = "Creating the 2016 database range"

'Here I created the "Mth_16_01_amt"     name and range.

'I need to replace "16" with "yy" and the "01" with "mm"

    ActiveWorkbook.Names.Add Name:="Mth_16_01_amt", RefersToR1C1:= _

        "='Data 2016'!R8C3:R183C4"

    ActiveWorkbook.Names("Mth_16_01_amt").Comment = _

        "creating the Jan. database range"

'Here I created the "Mth_16_06_amt"     name and range.

'I need to replace "16" with "yy" and the "06" with "mm"

    ActiveWorkbook.Names.Add Name:="Mth_16_06_amt", RefersToR1C1:= _

        "='Data 2016'!R8C3:R183C9"

    ActiveWorkbook.Names("Amt_16_06_amt").Comment = _

        "creating the June database range"

'Here I created the "Mth_16_12_amt"     name and range.

'I need to replace "16" with "yy" and the "12" with "mm"

    ActiveWorkbook.Names.Add Name:="Mth_16_12_amt", RefersToR1C1:= _

        "='Data 2016'!R8C3:R183C4"

    ActiveWorkbook.Names("Mth_16_12_amt").Comment = _

        "creating the Dec. database range"

End Sub

Answer 1

The method I was trained on, a few years back, was to create 1 workbook, or Excel file and within this file you would have worksheets for each year.  Maybe this isn't the best methods going forward?

Hello again El Bee,

In principal I don't think there is anything wrong with the philosophy. However, you said the workbook contains many years of worksheets and you could eventually reach a limit on the number of defined names. Supposedly number of names is based on the available memory but remember the workbook is getting bigger and the number of names is increasing. See the following link for Excel Specs and Limits for xl2010. Google "excel specifications and limits 2010" and replace 2010 for other versions of Excel. (Applies to at bottom of page.)

https://support.office.com/en-nz/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

I have uploaded a very simple example workbook. It contains 2 worksheets with 2 names on each worksheet and these names are scoped to the Workbook. Try the following method of copying the worksheets and then view what occurs with the defined names.

• Right click the workbook tab name "Data 2015"
• Select Move or Copy.
• Check the box against Create copy.
• Select Move to end (This is optional).
• Click OK and it creates a copy with (2) at end of name.
• Rename the worksheet to "Data 2016"
• Select Name Manager and view the names. It has created the new worksheet with the same names but instead of being scoped to the Workbook, they are scoped to the worksheet.
• Check out the formulas in Col 6 and Col 15. You will see they are looking up the data from Col 1 and Col 10 and returning the values from Col2 and Col 11.
• The formulas in Col 5 and Col 15 produce random numbers so you can see that they are returning the values from the same sheet and not from the original sheet "Data 15" where the random formulas are returning different numbers.
• Therefore, if you use this method of copying the worksheets then you should be able to use the same defined names and there is no need to change them.
• I have also included code to perform the copy. Click the button on worksheet "Data 2015".
• The code contains some validation to ensure the old worksheets exist and that the new worksheets have not already been created.
• Following is a link to OneDrive with a zipped file of the example workbook. Download it and give it a try.

http://1drv.ms/1OI4Ein

Answer 2

I am wondering if you are looking for something like the following where you look for names containing particular characters (ie. 15) and then change those characters (ie. to 16).

Initially it tests that the "RefersTo" range is in the required worksheet and then tests if the name contains "_15_" and if it does then changes "_15_" to "_16_".

Including the underscores is not really necessary; I just did so to demonstrate that it is essential to include as much as possible to ensure that the correct characters combined with the wild cards are matched and then the correct characters are replaced.

Sub ReNameNames()

    Dim Nm As Name

    For Each Nm In ThisWorkbook.Names

        If Nm.RefersTo Like "*Data 2016*" Then  'Test if Range is within worksheet Data 2016

            If Nm.Name Like "*_15_*" Then       'Test if _15_ appears in name

                'Insert _16_ in lieu of _15_ in the name

                Nm.Name = WorksheetFunction.Substitute(Nm.Name, "_15_", "_16_")

            End If

        End If

    Next Nm

End Sub

Following added as an after thought. You can also use variables for the strings to find and replace as per the following example. Advantage of this is that you only need to edit the variables for each year. You could even use code to determine the years based on the current date and set the variables accordingly. (ie. subtract 1 from current year to get previous year and use current year for the replacement. Format function with format set to "yy" will give you the current year.)

Sub ReNameNames_2()

    Dim Nm As Name

    Dim strNewWs As String

    Dim strOldyy As String

    Dim strNewyy As String

    strNewWs = "Data 2016"

    strOldyy = "_15_"   'Underscores are optional but ensure string will be unique

    strNewyy = "_16_"

    For Each Nm In ThisWorkbook.Names

        If Nm.RefersTo Like "*" & strNewWs & "*" Then     'Test if Range is within worksheet strNewWs

            If Nm.Name Like "*" & strOldyy & "*" Then        'Test if strOldyy appears in name

                'Insert strNewyy in lieu of strOldyy in the name

                Nm.Name = WorksheetFunction.Substitute(Nm.Name, strOldyy, strNewyy)

            End If

        End If

    Next Nm

End Sub

Answer 3

How and where are the new worksheet created? Are the new worksheets in the same workbook or a new workbook?

Are you copying the old worksheets by right click the worksheet tab name and select Move or copy or how are you doing it?

Is the creation of the new worksheets being done with VBA or manually in the User Interface mode?

Is there only one worksheet involved for the names or multiple sheets?

Answer 4

Hello OssieMac,

I may have confused the issue by including the renaming of a worksheet; that portion of the macro works fine.  The problem is trying to create what I call database references.  These reference names contain the row/column range, or an individual cell reference like “$A$2:$H$133” or “B3”.

Using the “Name Manager” I can see all the reference names, database references, that I’ve created manually.  With the start of a new year it takes me quite some time to add these new reference names and I have to make sure I don’t miss any; otherwise other macros and/or formulas will fail if any of the names are missing or created missing a row and/or column range.

For instance,  I have a reference name called, “Mth_15_01_amt” this refers to a range of cells, in the worksheet called “Data 2015”, ='Data 2015'!$C$8:$D$183.  I need to create a new reference for “Mth_16_01_amt” to a range in a newly created worksheet called "Data 2016",.

It would look like:  ='Data 2016'!$C$8:$D$183.  The only difference between the 2 references is the name of the worksheet.

This is where I came up with the idea of “mm” for month and “yyyy” for century year and “yy” for a 2-digit year.

After setting these values to numeric values I would substitute them into the "ActiveWorkbook.Names.Add Name =" function.

Thus the macro example:

    'Here I created the "Mth_16_01_amt" name and range.

    'I need to replace "16" with "yy" and the "01" with "mm"

        ActiveWorkbook.Names.Add Name:="Mth_yy_mm_amt", RefersToR1C1:= _

            "='Data yyyy'!R8C3:R183C4"

        ActiveWorkbook.Names("Mth_yy_01_amt").Comment = _

            "creating the Jan. database range"

I hope this helps explain what I'm trying to do.

Thanks so much for sharing your time and knowledge on this subject with me.

El Bee

Answer 5

Not sure if I am understanding the problem correctly but the following might help you to understand a different method of using VBA to add names to a workbook. Also, if you rename a worksheet then the Refers to range will be automatically adjusted to reflect the new worksheet name.

    'Following VBA applies the name yyyy to a worksheet range

    Worksheets("Data 2015").Range("$D$8:$D$20").Name = "yyyy"

    'Renaming the worksheet automatically adjusts the Refers to range for yyyy

    Worksheets("Data 2015").Name = "Data 2016"

By using this method of programming, you can use a variable in the worksheet name like the following. (Note the included space when concatenating the worksheet name with the year variable.)

    Dim Yr As Long

    Yr = 2015

  'Following VBA applies a name to a worksheet range. Coding is different to your examples but easy to implement.

    Worksheets("Data " & Yr).Range("$D$8:$D$20").Name = "yyyy"

    'Rename the worksheet by incrementing Yr and it will automatically adjust the Refers to range for yyyy

    Worksheets("Data " & Yr).Name = "Data " & Yr + 1

If the above does not help then I need further explanation of what you are attempting to do.