Paste copied stuff at the very end of a row

Question

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

If you want just one value to be pasted to multiple cells then first identify what what range is (let's say you want to paste in 5 cells) and then simply use `.Range("B" & Lastrow & ":B" & lastrow+5).value = textbox1.text` — Siddharth Rout, Nov 09 '15 at 10:26

score 2 · Accepted Answer · answered Nov 09 '15 at 10:18

2

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

answered Nov 09 '15 at 10:18

Siddharth Rout

147,039
17
206
250

It works fine but its copying stuff from the wrong sheet? (The newly code in the question) – Swi Nov 09 '15 at 12:11
Yes that is because you have not fully qualified the range object. Change `Range("B85:S85").copy` to `Sheets("Tabelle2").Range("B85:S85").copy` :) Also you may want to read [THIS](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – Siddharth Rout Nov 09 '15 at 12:13
short question, why does this not work? `Sheets("Tabelle2").Range("B25:Q25,S25,B27:Q27,B29:Q29,B31:Q31,B33:Q33,B35:Q35,B37:Q3").copy` – Swi Nov 09 '15 at 14:32
Is there no chance? because i need it on multible sections. Shit shit shit :/ oh god – Swi Nov 09 '15 at 14:44
why does it works without pastespecial? why not with? :/ – Swi Nov 10 '15 at 08:01

Paste copied stuff at the very end of a row

1 Answers1