How to copy and paste to the next row

Question

I'm creating a database with a data entry page that then uses a button to paste it into the master sheet.

I'm used to dealing with code and editing existing stuff, but this is the first time Ive had to start from scratch.

What I would like it to do is Copy the data entry cells and paste into the corresponding column on the master sheet, starting a new row each time.

e.g. cell C5 on data entry sheet copies to column A on master file, C7 to column B etc.

Ive managed to get it to copy, but only doing each cell individually (ideally id like to copy and paste all in one go, as a range rather than each cell individually), but no matter what I try from all the pages on here I cant get it to go to the next row each time.

Copy and paste that works:

Sheets("RAW DATA").Range("Ak2").PasteSpecial Paste:=xlPasteValues
Sheets("Enter Accident").Range("Q19").Copy
Sheets("RAW DATA").Range("AL2").PasteSpecial Paste:=xlPasteValues
Sheets("Enter Accident").Range("Q21").Copy
Sheets("RAW DATA").Range("AM2").PasteSpecial Paste:=xlPasteValues
Sheets("Enter Accident").Range("Q23").Copy
Sheets("RAW DATA").Range("AN2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

What I tried and it didn't work:

Sheets("Enter Accident").Range("C3, C5, C7, C9, C11, C13 ,C15, C17, C19, C21, C23, C25, C27, C29, C31, G3, G11, G13, G15, G17, G19, G21, G23, G25, G31, L3, L11, L13, L15, L19, L21, Q3, Q5, Q7, Q9, Q11, Q15, Q17, Q19, Q21, Q23").Copy
Sheets("RAW DATA").Range("A2, B2, C2, D2, E2, F2, G2, H2, I2, J2, K2, L2, M2, N2, O2, P2, Q2, R2, S2, T2, U2, V2, W2, Z2, Y2, AA2, AB2, AC2, X2, AD2, AE2, AF2, AG2, AH2, AI2, AJ2, AK2, AL2, AM2, AN2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Neither of these work with any interation of adding the last row such as:

Dim lastrow As Range
Set lastrow = Sheets("RAW DATA").Cells.SpecialCells(xlCellTypeLastCell).EntireRow + 1

Is the issue that you want to jump 2 rows each time you move along 1 column? — SJR

SJR SJR · Accepted Answer · 2019-05-14T12:14:09

One approach. If your ranges are likely to vary, probably better to use OFFSET and a loop.

Sub x()

Dim vCopy, vPaste, i As Long

vCopy = Array("C3", "C5", "C7", "C9", "C11", "C13 ,C15", "C17", "C19", "C21", "C23", "C25", "C27", "C29", "C31", "G3", "G11", "G13", "G15", "G17", "G19", "G21", "G23", "G25", "G31", "L3", "L11", "L13", "L15", "L19", "L21", "Q3", "Q5", "Q7", "Q9", "Q11", "Q15", "Q17", "Q19", "Q21", "Q23")
vPaste = Array("A2", "B2", "C2", "D2", "E2", "F2", "G2", "H2", "I2", "J2", "K2", "L2", "M2", "N2", "O2", "P2", "Q2", "R2", "S2", "T2", "U2", "V2", "W2", "Z2", "Y2", "AA2", "AB2", "AC2", "X2", "AD2", "AE2", "AF2", "AG2", "AH2", "AI2", "AJ2", "AK2", "AL2", "AM2", "AN2")

For i = LBound(vCopy) To UBound(vCopy)
    Sheets("RAW DATA").Range(vPaste(i)).Value = Sheets("Enter Accident").Range(vCopy(i)).Value
Next i

End Sub

To avoid overwriting, find the last row and go down one cell.

Sub x()

Dim vCopy, i As Long, rPaste As Range

vCopy = Array("C3", "C5", "C7", "C9", "C11", "C13 ,C15", "C17", "C19", "C21", "C23", "C25", "C27", "C29", "C31", "G3", "G11", "G13", "G15", "G17", "G19", "G21", "G23", "G25", "G31", "L3", "L11", "L13", "L15", "L19", "L21", "Q3", "Q5", "Q7", "Q9", "Q11", "Q15", "Q17", "Q19", "Q21", "Q23")

Set rPaste = Sheets("RAW DATA").Cells(Rows.Count, 1).End(xlUp)(2)

For i = LBound(vCopy) To UBound(vCopy)
    rPaste.Value = Sheets("Enter Accident").Range(vCopy(i)).Value
    Set rPaste = rPaste.Offset(, 1)
Next i

End Sub

How to copy and paste to the next row

2 Answers