dynamically build name manager entry in excel vba using values from table

Question

I want to create a new entry in Name Manager based on the 1st and last cells of a table.

I have this hardcoded in the my macro for the current worksheet but I will have many worksheets with tables of various sizes.

ActiveWorkbook.Names.Add Name:="TitleRegion1.a6.h48.4", RefersToR1C1:= "=pw!R142C1"

Here is my scenario:

My worksheet has a table - Table3, with headers, that spans cells A6 to H49 I want my entry to have the name "TitleRegion1.a6.h49.4"

These are my variables.

TitleRegion = just a string,
1 = refers to the 1st table in the current worksheet,
a6 = start cell of table,
h49 = end cell of table,
worksheet 4 in workbook

Here is the macro using Record Macro

Sub Macro1()
Range("Table3[[#Headers],[Data Source]]").Select
ActiveWorkbook.Names.Add Name:="TitleRegion1.a6.h48.4", RefersToR1C1:= _
"=Table3[[#Headers],[Data Source]]"
End Sub`

[Data Source] - is the value of a6 I am not sure where to start? I am new to Excel VBA programming.

paul bica paul bica · Accepted Answer · 2015-06-14T02:45:34

You can experiment with the code bellow (don't use a working file until you see how it works)

Open a new file
Open VBA Editor (Alt + F11)
Insert a new module. From the menu: Insert -> Module
Paste the code in the new module
Click anywhere inside this code and run it (F5)

This is what it does:

Defines the table range for all sheets that will have a table, to "A6:H49" ( line 3 )
Defines a list of sheets that should not have a table: "|Sheet2|Sheet3|...|" ( line 4 )
Goes through each sheet and, if not on the excluded list, creates a new table on it
The new table name will be similar to your requirements: Title1.A6_H49.1

Excel will automatically generate a new Name when a table is created, based on table name

Here is the code (updated):

Option Explicit

Sub makeTables()
    Dim excludedSheets As String, sh As Worksheet, tbl As ListObject, i As Long
    Dim tblRngList As Object, headerList As Object, indx As Long, rng As String
    Set tblRngList = CreateObject("Scripting.Dictionary")
    Set headerList = CreateObject("Scripting.Dictionary")

    With tblRngList
        .Add Key:=1, Item:="A6:H49"     'Sheet 1: Key:=1 -> sheet index
        .Add Key:=2, Item:="A6:H22"     'Sheet 2: "A6:H22" -> Tbl region on Sheet2
        .Add Key:=3, Item:=""           'Sheet 3: No table
    End With
    With headerList
        .Add Key:=1, Item:="Header 1"   'Headers must be unique
        .Add Key:=2, Item:="Header 2"
        .Add Key:=3, Item:="Header 3"
        .Add Key:=4, Item:="Header 4"
        .Add Key:=5, Item:="Header 5"
        .Add Key:=6, Item:="Header 6"
        .Add Key:=7, Item:="Header 7"
        .Add Key:=8, Item:="Header 8"   'Should match total cols defined in ranges
    End With                            '"A6:H49" = 8 columns

    With ActiveWorkbook                 'Current Excel file
        For Each sh In .Worksheets      'Iterate through all sheets of current file
            With sh                     'Current sheet: Sheet1, Sheet2, Sheet3
                indx = .Index           'Current sheet index: 1, 2, 3
                rng = tblRngList(indx)  'Current table range: "A6:H49", "A6:H22", ""

                If Len(rng) > 0 Then                    'If tbl rng is not empty ("")
                    If .ListObjects.Count > 0 Then      'If any previous tables exist
                        For Each tbl In sh.ListObjects  'Go through each one
                            tbl.Delete                  'and delete it
                        Next
                    End If
                    'Create table ---------------------------------------------------
                    Set tbl = .ListObjects.Add(xlSrcRange, sh.Range(rng), , _
                                               xlYes, , "TableStyleMedium5")
                    'Set Table name -------------------------------------------------
                    tbl.Name = "Title" & .ListObjects.Count & "." & _
                                tblRngList(indx) & "." & indx
                    'Set Headers ----------------------------------------------------
                    With tbl
                        For i = 1 To .HeaderRowRange.Count
                            .HeaderRowRange(i) = headerList(i)
                        Next
                    End With
                End If
            End With
        Next
    End With
End Sub

In a new file with 3 default sheets, it will create one table on Sheet1 and one on Sheet2
For more than 3 sheets add more items to the list using lines like this:

.Add Key:=7, Item:="A1:Z999"
where the Key is the index of the sheet, and item is the range of the table on that sheet
You can change "TableStyleMedium5" to any style from "Table Tools" -> "Table Styles" section
Hold the mouse over any available style to see its name (remove all spaces from the name):

.

After you run it, check Name Manager for a list similar to this (depending on excluded sheets)

NameManager

dynamically build name manager entry in excel vba using values from table

1 Answers