Excel VBA: Copy a Table Column Data to Another Table Column

Update: Added missing ‘Sub End’

On Excel 2010, I created this VBA to copy data from a specific column on a specific table to another table on another spreadsheet to editing the values later without disturbing the original source data.

I am posting it here since I had to do multiple searches for the syntax.

    Sub copyColumn()
    'Delete current values prior to paste if values exist
    If Application.WorksheetFunction.CountA(Range("Table2[Column1]")) <> 0 _
    Then Range("Table2[Column1]").Delete

    'Select desired column to copy with destination option
    Range("Table1[[Column3]]").Copy _
    Destination:=Range("Table2[[Column1]]")
    Sub End

If you find this snippet of code useful, leave a comment.

5 thoughts on “Excel VBA: Copy a Table Column Data to Another Table Column

  1. Hi, thanks for sharing the snippet. This is what I’m looking for but I am getting Method ‘Range’ of object’ _Global’ failed.

    Here’s the code:

    Sub copyColumn()

    If Application.WorksheetFunction.CountA(Range(“Table65[Column1]”)) 0 _
    Then Range(“Table65[Column1]”).Delete

    Range(“Table1[[Column6]]”).Copy _
    Destination:=Range(“Table65[[Column1]]”)

    End Sub

    I’m using Excel 2010.

    Thanks,
    Sharon

  2. Hi, please disregard. I found where I went wrong. The code worked perfectly. I just thought Column6 is the right reference name but it wasnt. After I correctly entered the right column reference, I got it to transfer. Thank you for the code snippet.

  3. The code is Run time error ‘1004’: Method ‘Range of object’_Global failed.

    I am very keen to get this right and to copy a couple of columns from one table to another to avoid a circular reference caused by using the original table.

  4. That was a good help. Cheers mate! missed the end sub though šŸ˜‰
    I’ll come back and post the function that I will be using this in in case you find it helpful.

Leave a Reply