CODEWITHSUNDEEP
excelvba
Swi
Swi

Reputation: 125

Paste copied stuff at the very end of a row

I have a form where you fill stuff in and a specific part of it should be copied to another sheet at the end of the list.

With Sheets("Sheet1")
If Application.WorksheetFunction.CountA(.Columns(2)) <> 0 Then
    lastrow = .Cells(rows.Count, "B").End(xlUp).Row
Else
    lastrow = 1
End If
.Cells(lastrow + 1, "B") = "my new value"
End With

I have this code to find the last row and paste/write "my new value" in it. But i need that it pastes more than just one cell. I just need that it selects that part where it writes "my new value" in. I should be able to do the rest I'm now using the code below. But it still copies stuff from the sheet "Tabelle3" but it should copy the stuff from the sheet "Tabelle2"

Private Sub CommandButton1_Click()
    Dim lastRow As Long

    With Sheets("Tabelle3")
        If Application.WorksheetFunction.CountA(.Columns(1)) <> 0 Then
            lastRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1 '<~~ Add 1 here and not as you are doing
        Else
            lastRow = 1
        End If

        Sheets("Tabelle2").Select
        Range("B85:S85").copy
        Sheets("Tabelle3").Select

        '~~> Paste special
        .Range("C" & lastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
 End Sub

Upvotes: 2

Views: 67

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149325

You have to find the last empty row and then simply do a paste or pastespecial as shown below.

Sub Sample()
    Dim lastRow As Long

    With Sheets("Sheet1")
        If Application.WorksheetFunction.CountA(.Columns(2)) <> 0 Then
            lastRow = .Cells(Rows.Count, "B").End(xlUp).Row + 1 '<~~ Add 1 here and not as you are doing
        Else
            lastRow = 1
        End If

        Range("Z10:Z100").Copy

        '~~> Paste special
        .Range("B" & lastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
End Sub

The above code will copy the range "Z10:Z100" and do a pastespecial on the next available row in Col B. If you do not want to do a pastespecial and want to do a direct paste then see this

Sub Sample()
    Dim lastRow As Long

    With Sheets("Sheet1")
        If Application.WorksheetFunction.CountA(.Columns(2)) <> 0 Then
            lastRow = .Cells(Rows.Count, "B").End(xlUp).Row + 1 '<~~ Add 1 here and not as you are doing
        Else
            lastRow = 1
        End If

        Range("Z10:Z100").Copy .Range("B" & lastRow)
    End With
End Sub

Upvotes: 2

Related Questions