How can I copy columns from one sheet to another with VBA in Excel?

calendar_today Asked Jan 2, 2010
thumb_up 16 upvotes
history Updated April 16, 2026

Question posted 2010 · +6 upvotes

I’m trying to write a macro that copies the content of column 1 from sheet 1 to column 2 on sheet 2. This is how the module looks like but, when I run it, I get

Run time error 9, Subscript out of range.

Sub OneCell()
    Sheets("Sheet1").Select
    'select column 1 A1'
    Range("A1:A3").Select

    Selection.Copy
    Range("B1:B3").Select

    ActiveSheet.Paste

    Sheets("Sheet2").Select
    Application.CutCopyMode = False
End Sub

Accepted answer +16 upvotes

The following works fine for me in Excel 2007. It is simple, and performs a full copy (retains all formatting, etc.):

Sheets("Sheet1").Columns(1).Copy Destination:=Sheets("Sheet2").Columns(2)

“Columns” returns a Range object, and so this is utilizing the “Range.Copy” method. “Destination” is an option to this method – if not provided the default is to copy to the paste buffer. But when provided, it is an easy way to copy.

As when manually copying items in Excel, the size and geometry of the destination must support the range being copied.

Excel VBA objects referenced (5)

  • Application — Using events with the Application object
  • Application — Working with Other Applications
  • Range — Refer to Cells by Using a Range Object
  • Range — Delete Duplicate Entries in a Range
  • Selection — Working with the Selection Object

Top excel-vba Q&A (6)

+16 upvotes ranks this answer #27 out of 136 excel-vba solutions on this site — top 20%.
excel-vba