Name new table in Macro?

[aythieriot]

Hi,

I'm creating a macro that copies a table from one sheet to another. I then have to reformat the new table so that most of the fields are formatted as "output" cells that are protected. The issue lies in that I try to format subtitles by filtering in the new table (using MOD and remainders, i.e. just 0s and 1s), but the Macro doesn't recognize the target area because each time it gets copied, its Table115, Table116, etc.

I tried renaming the new table each time it is copied, but again I have to reference the new table whose name has once again changed...thoughts?

Thanks

 

[Jerry Sullivan]

I tried renaming the new table each time it is copied, but again I have to reference the new table whose name has once again changed...thoughts?

Hi aythieriot,

You can reference Tables in VBA using the ListObjects Collection of the Worksheet.

With Sheets("Sheet1")
    MsgBox "Last Table Name is: " & _
        .ListObjects.Item(.ListObjects.Count).Name
End With

 

[aythieriot]

Hey Jerry,

Thanks for the help, I think it´s a step in the right direction. The issue is that the new table whose name changes with each macro use is only halfway into my macro, therefore all the rest of the steps won't work unless I can consistently rename the new table the same time each macro use so that the following references properly format said new table.

Ideas?

Thanks again,


Drew

 

[Jerry Sullivan]

Hola Drew,

You can capture the name of the new table as a variable then use that to reference the table for the rest of your macro.

For example, to add a column to your table and then add row numbers in that column:

Sub MyMacro()
    Dim strTableName As String
    Dim lngColCount As Long
    Sheets("Sheet1").Range("Table1[#All]").Copy _
        Destination:=Sheets("Sheet2").Range("A1")
    With Sheets("Sheet2")
        strTableName = .ListObjects.Item(.ListObjects.Count).Name
        With .ListObjects(strTableName)
            lngColCount = .ListColumns.Count
            With .DataBodyRange.Resize(, 1).Offset(0, lngColCount - 1)
                .FormulaR1C1 = "=Row()"  'add row numbers for new col
            End With
        End With
    End With
End Sub

If that approach doesn't appear to work for you, please post some of your code and that will make it easier to help.