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

User · Answer

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.

User · Answer

Selecting is often unnecessary  Try this  Sub OneCell       Sheets  Sheet2   range  B1 B3   value   Sheets  Sheet1   range  A1 A3   value End Sub

User · Answer

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

User · Answer

Private Sub Worksheet Change ByVal Target As Range    Dim rng As Range  r As Range   Set rng   Intersect Target  Range  a2 a   amp  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 · Answer

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

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

Examples related to vba

Examples related to excel