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

Question

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

David · Answer 1 · 2012-12-19T00:39:34.353

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.

score 19 · Answer 2 · answered Jan 02 '10 at 21:12

19

Selecting is often unnecessary. Try this

Sub OneCell()
    Sheets("Sheet2").range("B1:B3").value = Sheets("Sheet1").range("A1:A3").value
End Sub

answered Jan 02 '10 at 21:12

guitarthrower

5,624
3
29
37

1

This is the better solution than the solution above – Aaron Oct 11 '16 at 09:07
2

This was faster than David's answer from 18'12 – E_L Jan 18 '18 at 09:48
1

Much better because it doesn't actually use copying, it's directly setting values. – jamheadart Jun 25 '20 at 14:01
The OP didn't specify exactly what was to be copied, and the top-ranked answer makes no assumptions, and copies everything. Of course, if you do only want to copy cell values, this answer is very good, and likely has the best performance. – David Jul 28 '20 at 22:10

score 2 · Answer 3 · edited Feb 09 '12 at 16:08

2

If you have merged cells,

Sub OneCell()
    Sheets("Sheet2").range("B1:B3").value = Sheets("Sheet1").range("A1:A3").value
End Sub

that doesn't copy cells as they are, where previous code does copy exactly as they look like (merged).

edited Feb 09 '12 at 16:08

Basic

26,321
24
115
201

answered Feb 09 '12 at 14:38

Lucy

21
2

paxdiablo · Answer 4 · 2010-01-02T04:55:02.563

I'm not sure why you'd be getting subscript out of range unless your sheets weren't actually called Sheet1 or Sheet2. When I rename my Sheet2 to Sheet_2, I get that same problem.

In addition, some of your code seems the wrong way about (you paste before selecting the second sheet). This code works fine for me.

Sub OneCell()
    Sheets("Sheet1").Select
    Range("A1:A3").Copy
    Sheets("Sheet2").Select
    Range("b1:b3").Select
    ActiveSheet.Paste
End Sub

If you don't want to know about what the sheets are called, you can use integer indexes as follows:

Sub OneCell()
    Sheets(1).Select
    Range("A1:A3").Copy
    Sheets(2).Select
    Range("b1:b3").Select
    ActiveSheet.Paste
End Sub

score -1 · Answer 5 · edited Mar 17 '12 at 19:26

-1

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, r As Range
  Set rng = Intersect(Target, Range("a2:a" & Rows.Count))
  If rng Is Nothing Then Exit Sub
    For Each r In rng
      If Not IsEmpty(r.Value) Then
        r.Copy Destination:=Sheets("sheet2").Range("a2")
      End If
    Next
  Set rng = Nothing
End Sub

edited Mar 17 '12 at 19:26

Juan Mellado

14,973
5
47
54

answered Mar 17 '12 at 16:58

Ravi

11

5

Next time, add a explanation to your solution – Juan Mellado Mar 17 '12 at 19:27

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

5 Answers5

Linked