CODEWITHSUNDEEP
excelvba
Prashant Balasubramanyan
Prashant Balasubramanyan

Reputation: 129

Excel Vba: Using table name as a variable

I am trying to copy the contents of one table to another. in the process I am copying only some columns. Sorry if my question is too basic as I'm new to Excel VBA

Dim tableName As ListObject
Set tableName = Worksheets("DataSource").ListObjects(1)
[Table1[TaskUID]].Copy [tableName[TaskUID]]

Line 3 is throwing an error "Object Required"

Could anyone please help with the syntax

Upvotes: 2

Views: 9291

Answers (2)

QHarr
QHarr

Reputation: 84475

With actual table names:

You can wrap with Range and use the actual table names. You are effectively calling the .Copy method on a named range. So if they were in the same sheet for example:

With ActiveSheet

    .Range("Table1[TaskUID]").Copy .Range("Table2[TaskUID]")

End With

You can always qualify the destination with the sheet name if it is in another sheet.

Or (by column number of table variable):

As you need a range to be used for the copy destination, if you know the column number of the table variable to paste to, you can also write, using the table variable name something like:

.Range("Table1[TaskUID]").Copy  tableName.DataBodyRange.Columns(2) 'or which ever column

Or (by column name of table variable):

.Range("Table1[TaskUID]").Copy tableName.ListColumns("TaskUID").DataBodyRange

You could place tableName.ListColumns("TaskUID").DataBodyRange in its own variable and then refer to that e.g.

Dim rng As Range
Set rng = tableName.ListColumns("TaskUID").DataBodyRange

.Range("Table1[TaskUID]").Copy rng

Edit: As GSerg points out you can't use the variable name inside. "Range will not be able to find a table named tableName because that is the name of a variable, not of a table"

Upvotes: 2

GSerg
GSerg

Reputation: 78210

The reason it does not work is because using the brackets, [], is like calling Application.Evaluate() on the expression inside the brackets, so you cannot use your local variable names in there because the thing that will be evaluating the expression has no idea about them. It cannot find a list object named tableName.

You can avoid the brackets (arguably, you always should):

Dim tableName As ListObject
Set tableName = Worksheets("DataSource").ListObjects(1)

ListObjects("Table1").ListColumns("TaskUID").DataBodyRange.Copy tableName.ListColumns("TaskUID").DataBodyRange

Upvotes: 2

Related Questions