Copy Paste Data into First empty Row VBA

Question

I have wrote a below code which is not working.

The thing i want from this code is to copy the Sheet4 cells and paste them into the Sheet2 given cells in the first empty row.

I have tried to find an way but nothing comes which could help. Your help will be appreciated.

Receiving an error

Sub Save()
    Dim NextRow As Range
    Set NextRow = Range("A" & Sheets("Sheet2").UsedRange.Rows.Count + 1)
    Sheet4.Range("G7" & "H7" & "I7" & "J7" & "K7" & "L7" & "M7" & "N7").Copy
    Sheet2.Activate
Sheet2.Range ("A2" & "C2" & "E2" & "F2" & "H2" & "J2" & "L2" & "M2")
NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
    Application.CutCopyMode = False
    Set NextRow = Nothing
End Sub

But it does not go for the next Row over running the code.

Sub Get_Data()
With Sheet4
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
Sheet4.Range("G7").Copy
Sheet2.Range("A" & lastrow).PasteSpecial xlPasteValues
Sheet4.Range("H7").Copy
Sheet2.Range("C" & lastrow).PasteSpecial xlPasteValues
Sheet4.Range("I7").Copy
Sheet2.Range("E" & lastrow).PasteSpecial xlPasteValues
Sheet4.Range("J7").Copy
Sheet2.Range("F" & lastrow).PasteSpecial xlPasteValues
Sheet4.Range("K7").Copy
Sheet2.Range("H" & lastrow).PasteSpecial xlPasteValues
Sheet4.Range("L7").Copy
Sheet2.Range("J" & lastrow).PasteSpecial xlPasteValues
Sheet4.Range("M7").Copy
Sheet2.Range("L" & lastrow).PasteSpecial xlPasteValues
Sheet4.Range("N7").Copy
Sheet2.Range("M" & lastrow).PasteSpecial xlPasteValues
End Sub

VBasic2008 · Accepted Answer · 2021-02-16T14:30:40.233

1

Copy by Assignment

Option Explicit

Sub copyByAssignment()
    
    ' Constants (Destination)
    Const dColsList As String = "A,C,E,F,H,J,L,M"
    
    ' Source
    Dim srg As Range: Set srg = Sheet4.Range("G7:N7")
    
    ' Destination
    Dim dRow As Long
    dRow = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row + 1
    Dim dCols() As String: dCols = Split(dColsList, ",") ' zero-based '(i - 1)'
    
    ' Copy by Assignment
    Dim i As Long
    For i = 1 To srg.Cells.Count
        Sheet2.Cells(dRow, dCols(i - 1)).Value = srg.Cells(i)
    Next i

End Sub

EDIT:

Sub copyByAssignmentLists()
    
    ' Constants
    Const sRow As Long = 7
    Const sColsList As String = "A,C,E,F,H,J,L,M"
    Const dColsList As String = "A,C,E,F,H,J,L,M"
    
    ' Source
    Dim sCols() As String: sCols = Split(sColsList, ",")
    
    ' Destination
    Dim dCols() As String: dCols = Split(dColsList, ",")
    Dim dRow As Long
    dRow = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Copy by Assignment
    Dim n As Long
    For n = 0 To UBound(sCols)
        Sheet2.Cells(dRow, dCols(n)).Value = Sheet4.Cells(sRow, sCols(n)).Value
    Next n

End Sub

edited Feb 16 '21 at 14:30

answered Feb 16 '21 at 14:18

VBasic2008

44,888
5
17
28

Thank you very much for the solution. @VBasic2008 I also wants to use the Sheet4.Range("G7:N7") as cells .Range("G7,H7,I7,J7'K7,L7,M7,N7") can you please look into this once more. – Feb 16 '21 at 14:21
1

Added the 'lists' version. – VBasic2008 Feb 16 '21 at 14:31
thank you very much but its not working because Both sheets Columns are different. Here it should (Const dColsList As String = "A,C,E,F,H,J,L,M") be like .Range("G7,H7,I7,J7'K7,L7,M7,N7") – Feb 16 '21 at 14:33
1

They can be different, but there has to be the same number of columns. In this example, both ranges have 8 columns. Or do you mean to use "G,H,I,J,K,L,M,N"? Just replace the values in the list as necessary. `7` is covered by the `sRow` variable. – VBasic2008 Feb 16 '21 at 14:34
Oh Ok i got your point thank you very much @VBasic2008 – Feb 16 '21 at 14:40

score 0 · Answer 2 · answered Feb 16 '21 at 13:48

There are a couple of issues with your code:

You don't seem to have a variable Sheet2 declared, however you are using it - it will not compile (unless it's a global/module variable)
You cannot put such a string ("G7" & "H7" &...) in Range method as a parameter. Try replacing it by Range("G7:N7"), which refers to all cells G7 through N7. IF you need descrete ranges to be copied either copy them one by one or use Union.
In line 6 you just state Sheet2.Range(...) - what't the reason behind this. It does nothing.

I would guess that the use of Range is the error VBA complains about. However, I would recommend to see a ready working solution and try to understand it or read about the use of methods you use. Not to repeat other SO threads, here's a good one: How do I copy a range of formula values and paste them to a specific range in another sheet?

Thank you for the Answer it will definitely help. @SnowGroomer — , Feb 16 '21 at 13:58
I have made the code but it does not insert data in next Row when i run the code again. Please see the above edited code. — , Feb 16 '21 at 14:00

Copy Paste Data into First empty Row VBA

2 Answers2

Copy by Assignment