Copy Columns from One Table to Another

Copy Column Application

The requirement to copy and paste columns from one table to another in a different order and to add formulas is very common.

Here is a bit of code that can accomplish that task.

It all depends on the Configuration Sheet and the Table, Table_Columns. Copy this sheet as is into your workbook and then import the module (BAS File)

See documentation in ZIP file for more details.

Sub CopyColumn(TableNameTo As String, TableNameFrom As String)
Dim clC As Range            ' Pointer to TableTo column names
Dim TypeCopy As String      ' Copy as column or formula

For Each clC In Range("Table_Columns[Column To]")
    TypeCopy = Left(clC.Offset(0, 1).Value, 1)
    If TypeCopy = "=" Then
        ' It is a formula
        Range(TableNameTo & "[" & clC.Value & "]") = clC.Offset(0, 1).Value
    Else
        ' It is a columnh
        Range(TableNameFrom & "[" & clC.Offset(0, 1).Value & "]").Copy
            Range(TableNameTo & "[" & clC.Value & "]").PasteSpecial xlPasteValues
    End If
Next clC
        
End Sub