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
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
Worksheets("sheet containing table1"). in front of it.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"
sheetName.Range("tableName[TaskUID]") won't work for the same reason as the original OP's code - Range will not be able to find a table named tableName because that is the name of a variable, not of a table. Your first example will work, but only because you are using hard-coded table names Table1 and Table2 instead of the variable name, at which point it would work as [Table1[TaskUID]].Copy [Table2[TaskUID]] too.