Table Reference when copying a worksheet

Question

Second part of a question which has been answered on here First Part Of Question

I have a worksheet that contains a table that has some code to add columns as and when needed. This works perfectly (thanks to answers on this site) but within the code it references the table name (in this case it is a table called 'Labour'). The worksheet is also a blank template that again has some code that copies the entire worksheet and pastes it to the end of the workbook for the next new task.

The problem I have now is that as I have a table on the template worksheet, when the worksheet is copied to a new worksheet the table name changes (ie if it was table1 it is then named table2 on the new sheet). This in turn stops the code working for adding a new column into the new table. Is there anyway to have the code 'know' what table is on the sheet and use that?

I can get the table name to appear in a cell, would there be a way to use that?

The code for adding a column to the table is:

Sub AddNewColumn()
    Application.ScreenUpdating = False
    Dim oSh As Worksheet
    Dim oList As ListObject
    Dim str As String

    Set oSh = ActiveSheet
    Set oList = oSh.ListObjects("Labour")

    With oList
            .ListColumns.Add
            str = .ListColumns(1).Name
            Range("Labour[[#All],[Column16]]").Select
            Selection.Copy
            .ListColumns(.ListColumns.Count).DataBodyRange.PasteSpecial xlPasteAll
    Application.ScreenUpdating = True
    End With
    End Sub

As you can see the table name "Labour" is within the code

Set oList = oSh.ListObjects("Labour")

So when adding a new column on the new worksheet I get an error (because the table Labour doesn't exist on the new sheet).

score 0 · Answer 1 · answered Jan 22 '18 at 09:04

Scrap this question I think I have figured it out. I got VBA to use the cell with the table name in so it could use a variable. Code as follows:

Sub AddNewColumn()
  Application.ScreenUpdating = False
    Dim oSh As Worksheet
    Dim oList As ListObject
    Dim tblName As String
    Dim str As String

    tblName = Range("B18").Text

    Set oSh = ActiveSheet
    Set oList = oSh.ListObjects(tblName)

    With oList
            .ListColumns.Add
            str = .ListColumns(1).Name
            Range(tblName & "[[#All],[Column16]]").Select
            Selection.Copy
            .ListColumns(.ListColumns.Count).DataBodyRange.PasteSpecial xlPasteAll
  Application.ScreenUpdating = True
    End With
End Sub

I've left this just in case it could help anyone else.

Table Reference when copying a worksheet

1 Answers1