1

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.

braX
  • 11,506
  • 5
  • 20
  • 33
dl2101
  • 11
  • 2
  • 1
    Use `PasteSpecial` with `Transpose:=True`. – BigBen Mar 05 '20 at 12:43
  • Do you like to al the time be pasted in "A2", or in the next empty row? – FaneDuru Mar 05 '20 at 12:44
  • @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 Mar 05 '20 at 12:56
  • Does this answer your question? [Excel VBA - Range.Copy transpose paste](https://stackoverflow.com/questions/8852717/excel-vba-range-copy-transpose-paste) – BigBen Mar 05 '20 at 12:57
  • Remove the `_` - this should be two separate lines and that's the line continuation. – BigBen Mar 05 '20 at 12:57
  • `Range("A2:D2")` -> `Range("A2")`. – BigBen Mar 05 '20 at 12:58
  • @BigBen ah perfect that worked! Thanks a lot – dl2101 Mar 05 '20 at 12:59
  • @FaneDuru The next empty row – dl2101 Mar 05 '20 at 12:59
  • `Range("A2")` -> `Range("A" & Rows.Count).End(xlUp).Offset(1)`. – BigBen Mar 05 '20 at 13:05
  • @BigBen This gives an error message Sub Draft() Worksheets("Material Check").Range("B3:B6").Copy Worksheets("Archive").Range("A2:D2" & Rows.Count)End(x1Up).Offset(1).PasteSpecial Transpose:=True End Sub – dl2101 Mar 05 '20 at 13:11
  • Make sure you copy *exactly* what I have. You've got a couple issues there. – BigBen Mar 05 '20 at 13:13
  • Sub Draft() Worksheets("Material Check").Range("B3:B6").Copy Worksheets("Archive").Range("A2" & Rows.Count).End(xlUp).Offset (1) End Sub This throws another error – dl2101 Mar 05 '20 at 13:17
  • You still didn't copy exactly what I have - I don't have `A2`, but `A`. There's a big difference :-) – BigBen Mar 05 '20 at 13:24
  • I tried with just A and A2. Thats why I sent the code with A2 because that was the last one I tried. But both don't work. The code I had before trying to insert after the last row worked great. Just need to get it to insert after the final row. – dl2101 Mar 05 '20 at 13:33
  • `Worksheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Transpose:=True`. Please copy (don't retype) this line :-) – BigBen Mar 05 '20 at 13:34
  • That worked great now, thank you much appreciated! Is it possible to clear the formatting after its done that? As its making one of the cells on the archive page red which I don't want. – dl2101 Mar 05 '20 at 13:44
  • `Worksheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Transpose:=True`. – BigBen Mar 05 '20 at 13:47
  • Perfect! Thank you! – dl2101 Mar 05 '20 at 13:56
  • I'm not sure if this is possible but are you able to copy separate ranges off cells of the same worksheet and onto a separate one. So for example copy cells B3:B6 and F4:F7 from the Material check sheet onto the archive sheet and on the same row? – dl2101 Mar 05 '20 at 15:10

1 Answers1

0

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
Gravitate
  • 2,885
  • 2
  • 21
  • 37
  • This doesn't bring over the formatting though, which OP might need (or might not, can't tell from the question). – BigBen Mar 05 '20 at 13:18
  • No, but I took "copy data from a range of cells" to mean data only. – Gravitate Mar 05 '20 at 13:24
  • I don't need the formatting to carry over. But I tried the code and it didnt throw any error messages up and ran but it didn't actually paste any of the data. Also, this is my first time doing VBA which would explain by very minimal understanding and poorly worded questions haha – dl2101 Mar 05 '20 at 13:38
  • Are you sure you copied the code exactly? I just tried it and it works fine for me... Are all the sheet names and ranges correct? I just noticed that you also want the data adding to the bottom of the list. For this, I'd suggest using an actual table. – Gravitate Mar 05 '20 at 13:44