Copying the last column and pasting next to it | Excel forum

Resolved · Medium Priority · Version 365

Wed 7 Jul 2021, 08:47 Delegate Jo said...

Jo has attended:
Excel VBA Introduction course
Excel VBA Intermediate course

Copying the last column and pasting next to it

Hi,

I have many spreasheets that need updating regularly, and that involves copying that last column of the data and pasting it in the empty column to it's right. So far I have managed to work out the copy bit, but am struggling to get it to paste in the next column, any ideas?

Here's what I have so far:

Sub LastColumn()
Dim LastCol As Long

With ThisWorkbook.Sheets("LO TVM Data")
LastCol = Cells(1, .Columns.Count).End(xlToLeft).Column
Columns(LastCol).Copy

End With

End Sub

Thanks,

Jo

Wed 7 Jul 2021, 09:11 Trainer Marius said...

RE: Copying the last column and pasting next to it

Hello Jo,

Thank you for your question. Here is a solution that works for me:

Sub LastColumn()
Dim LastCol As Long

With ThisWorkbook.Sheets("LO TVM Data")
LastCol = Cells(1, .Columns.Count).End(xlToLeft).Column
Columns(LastCol).Copy Destination:=Cells(1, LastCol + 1)

End With

End Sub

I added: Destination:=Cells(1, LastCol + 1)

This line simply instructs Excel to paste the last column's data one column to the right.

I hope this works for you.

Kind regards
Marius Barnard
STL