Copy a range of cells from one worksheet to another using VBA

Question

I'm new to using VBA and need to copy data from a range of cells on one worksheet to another worksheet. I need to copy a column of cells and paste it into a row of cells e.g. A1:A4 to A1:D1. This is the code i'm using but it doesn't work the way i need it too.

Sub Draft()

Worksheets("Material Check").Range("B3:B6").Copy _
Destination:=Worksheets("Archive").Range("A2:D2")

End Sub

Also I need the data thats being copied over to be added to the bottom of the table on the archive sheets and i'm not sure how to do this.

Do you like to al the time be pasted in "A2", or in the next empty row? — FaneDuru
@BigBen I edited my code to this but it throws up an error message Sub Draft() Worksheets("Material Check").Range("B3:B6").Copy _ Worksheets("Archive").Range("A2:D2").PasteSpecial Transpose:=True End Sub — dl2101
Does this answer your question? Excel VBA - Range.Copy transpose paste — BigBen
Remove the _ - this should be two separate lines and that's the line continuation. — BigBen

Gravitate Gravitate · Accepted Answer · 2020-03-05T12:58:39

Without Excel Tables

This is a bit of an odd way to do it but if you have a lot of cells to do, it's possibly faster than copy/paste special:

ThisWorkbook.Worksheets("Archive").Range("A2:D2").Formula = "=INDEX('Material Check'!$B$3:$B$6,Column())"
ThisWorkbook.Worksheets("Archive").Range("A2:D2").Value = ThisWorkbook.Worksheets("Archive").Range("A2:D2").Value

The first line populates the destination range with a formula that pulls the data from the source, using INDEX/COLUMN to transpose the result.

The second line simply converts the formula to hard values.

EDIT - Solution to copy the values to the bottom of the list
Using Excel Table

To do this you will need to go to "Insert" --> "Table".

''Get a reference to your destination table
Dim Tbl1 As ListObject
Set Tbl1 = ThisWorkbook.Sheets("Archive").ListObjects("Table1") ''Change these to your destination sheet/table names

''add a new row to the table
Dim Newrow As ListRow
Set Newrow = Tbl1.ListRows.Add

''populate the new row
With Newrow
    .Range(Tbl1.ListColumns("Column1").Index) = ThisWorkbook.Worksheets("Material Check").Range("B3") ''change these to your destination column name and your source sheet/ranges
    .Range(Tbl1.ListColumns("Column2").Index) = ThisWorkbook.Worksheets("Material Check").Range("B4")
    .Range(Tbl1.ListColumns("Column3").Index) = ThisWorkbook.Worksheets("Material Check").Range("B5")
    .Range(Tbl1.ListColumns("Column4").Index) = ThisWorkbook.Worksheets("Material Check").Range("B6")
End With

Copy a range of cells from one worksheet to another using VBA

1 Answers