CODEWITHSUNDEEP
excelvba
user15169505
user15169505

Reputation:

Copy Paste Data into First empty Row VBA

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 enter image description here

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

Upvotes: 0

Views: 1480

Answers (2)

VBasic2008
VBasic2008

Reputation: 55038

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

Upvotes: 1

SnowGroomer
SnowGroomer

Reputation: 695

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?

Upvotes: 0

Related Questions