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.
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
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.
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.
The Run time error ā1004? may be due to the missing ‘End Sub’ from the snippet.
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.
STRUCTURED TABLE REFERENCING. It’s called structured table referencing.