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

user_242035 0
Viewed 275332

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
vba excel
5 Answers

Selecting is often unnecessary. Try this

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

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
user_148600 
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
user_12759980
Related