How to do a VBA code to grab only some columns from a selection, and paste to the end of another sheet

Question

I have a workbook with several sheets, each one has information about a different product. And the 1st sheet is a summary with only part of the data. What happens currently is that after filling data in the product sheet, someone has to manually copy the relevant info and paste it in the summary sheet. So I'm trying to write a macro, so the person selects the recently added rows, presses a button, and the macro copies it to the 1st sheet. However, I don't want to copy all columns. I want to grab all the selected rows, but only columns A,B,E,F,G. This is what I have so far.

Set range1 = Selection.Resize(,2)
range1.Copy
With Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1)
  .PasteSpecial(xlPasteAll)
  .PasteSpecial(xlPasteValues)
End With

This works successfully for copying columns A,B. Now I'm struggling to copy the other 3. I have been playing with Range, Resize, Union. I know my answer lies somewhere around there. But since I'm not familiar with VBA, I'm hoping someone can help me fast forward the process a bit.

Thanks!

In general, you want to [avoid using selection](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. That link should also guide you towards a solution. — cybernetic.nomad, Oct 06 '21 at 17:55
@cybernetic.nomad - seems like a legitimate use of Selection here? User has to select some rows before running the code. — Tim Williams, Oct 06 '21 at 18:04
@Tim Williams: user mentions wanting to select columns A,B,E,F,G, so I, personally would avoid the selection — cybernetic.nomad, Oct 06 '21 at 22:16

Tim Williams · Answer 1 · 2021-10-06T18:29:49.247

Try this:

Dim sel as Range
Set sel = Selection.EntireRow.Columns(1) 'in case user selects outside of colA

sel.Resize(,2).Copy
With Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1)
   .PasteSpecial(xlPasteAll)
   .PasteSpecial(xlPasteValues)

   sel.offset(0, 4).Resize(,3).Copy
   .Offset(0, 2).PasteSpecial(xlPasteAll)
   .Offset(0, 2).PasteSpecial(xlPasteValues)
End With

Edit: removing duplication using a second method to perform the copy/paste

Sub Tester()
    Dim sel As Range, cDest As Range
    
    Set sel = Selection.EntireRow.Columns(1) 'in case user selects outside of colA
    Set cDest = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1)
    
    Transfer sel.Resize(, 2), cDest
    Transfer sel.Offset(0, 4).Resize(, 3), cDest.Offset(0, 2)
End Sub

'transfer rngSrc to rngDest using copy/pastespecial
Sub Transfer(rngSrc As Range, rngDest As Range)
    rngSrc.Copy
    With rngDest.Cells(1) 'top-left cell in case >1 cell in rngDest
        .PasteSpecial xlPasteAll
        .PasteSpecial xlPasteValues
    End With
End Sub

That worked beautifully!! Thank you so much for the fast answer. :-) — Marklar, Oct 06 '21 at 18:16
@Marklar if the answer solves your problem, please mark it as correct so others will know there's a useful answer — cybernetic.nomad, Oct 06 '21 at 22:17

How to do a VBA code to grab only some columns from a selection, and paste to the end of another sheet

1 Answers1