Excel VBA: Using a variable as table name

Question

When new year comes, new worksheet is created and so is a table in this new worksheet. I've recorded a macro to create a table. A simple function (as seperate module) returns current year so new worksheet gets its name and I wanted to name the table in this new worksheet with current year but it doesn't work properly. When I check the table name after it's created it's like _2016.

After creating the table macro is naming columns with my names but it crashes with first column with error method range of object _global failed.

I am not sure how to properly pass the result of my function to name the table and use it to refer to the table in the macro to change columns names. When macro was recorded instead of:

Range("Data.GetYear[[#Headers],[Kolumna1]]").Select

there was:

Range("Table1[[#Headers],[Kolumna1]]").Select

and it worked so basically the question is how to replace Table1 with a variable so I could refer easily to the table as there can't be multiple tables with the same name in a workbook.

Function GetYear() As String

GetYear = Format(Date, "yyyy")

End Function

Creating table:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$8:$F$8"), , xlYes).Name = Data.GetYear
    Range("Data.GetYear[[#Headers],[Kolumna1]]").Select
    ActiveCell.FormulaR1C1 = "Lp."
    Range("Data.GetYear[[#Headers],[Kolumna2]]").Select
    ActiveCell.FormulaR1C1 = "Data przychodu"
    Range("Data.GetYear[[#Headers],[Kolumna3]]").Select
    ActiveCell.FormulaR1C1 = "Kwota przychodu"
    Range("Data.GetYear[[#Headers],[Kolumna4]]").Select
    ActiveCell.FormulaR1C1 = "Podatek"
    Range("Data.GetYear[[#Headers],[Kolumna5]]").Select
    ActiveCell.FormulaR1C1 = "Dochód"
    Range("Data.GetYear[#All]").Select
    Selection.Columns.AutoFit
    Range("Data.GetYear[[#Headers],[Lp.]]").Select
    Selection.AutoFilter

Answer 1

I've written a short Workbook_Open event macro. If the worksheet or table for a new year's data does not exist, it automatically creates them. Essentially, the first time the workbook is opened in the new year, both the worksheet and table will be created.

You can review this working code to see how I handled both the Worksheet/ListObject naming as well as populating the table headers.

Option Explicit

Private Sub Workbook_Open()
    On Error GoTo bm_New_Year_ws
    With Worksheets(Chr(95) & Year(Date))
        On Error GoTo bm_New_Year_tbl
        With .ListObjects(Format(Date, "\t\a\b\l\e0000"))
            .Parent.Select
            .Parent.Range("B9").Select
        End With
    End With

    GoTo bm_Safe_Exit

bm_New_Year_tbl:
    On Error GoTo 0
    With Worksheets(Chr(95) & Year(Date))
        .Range("B8:F8") = Array("Lp.", "Data przychodu", _
                                "Kwota przychodu", _
                                "Podatek", "Dochód")
        With .ListObjects.Add(xlSrcRange, .Range("B8:F9"), , xlYes)
            .Name = Format(Date, "\t\a\b\l\e0000")
            .Range.Columns.AutoFit
        End With
    End With
    On Error GoTo bm_Safe_Exit
    Resume
bm_New_Year_ws:
    On Error GoTo 0
    With Worksheets.Add(after:=Sheets(Sheets.Count))
        .Name = Chr(95) & Year(Date)
    End With
    On Error GoTo bm_Safe_Exit
    Resume
bm_Safe_Exit:

End Sub

Answer 2

Table names cannot be purely numbers. They must be text, which is why preceeding a number with the underscore character is allowed as a table name, but just the number isn't.

I'd recommend finding some kind of standard naming convention which is textual, but yet is the year desired. Some examples might be:

Table2015
2015Table
_2015
2015Data
2015Whatever...

This is the routine I use for checking if a table exists in a given workbook or not...

Function TableExists(ByVal TableName As String, Optional ByVal TableSheet As Worksheet) As Boolean
'
' Returns True if a Table exists with the specified name on the specified (or active) sheet.
'
' Author:       Zack Barresse
'
' Syntax:       TableExists(TableName,[TableSheet])
'
' Parameters:   TableName. String. Required. Table name to check if it exists.
'               TableSheet. Worksheet. Optional. Worksheet to look for Table in. The active sheet
'               is used if not specified
'
    If TableSheet Is Nothing Then
        If ActiveSheet Is Nothing Then Exit Function
        Set TableSheet = ActiveSheet
    End If
    On Error Resume Next
    TableExists = CBool(Len(TableSheet.ListObjects(TableName).Name) <> 0)
    On Error GoTo 0

End Function

HTH Zack Barresse

Answer 3

Googling more for a solution, I found out that this line:

Range("Data.GetYear[[#Headers],[Kolumna1]]").Select

should look like this:

Range(Data.GetYear & "[[#Headers],[Kolumna1]]").Select

and at the moment my line looks like this:

Range(Nazwa & "[[#Headers],[Kolumna1]]").Select

where Nazwa is a simple string function:

Function Nazwa() As String

Nazwa = "Najem_" & Year(Date)

End Function

And that's all what's necessary to use a variable as a table name. So simple and took so much time to solve this.