Paste to last row +1

[PeterJohns27]

Selecting row from another worksheet , copy to next empty row in another worksheet. Though selecting the correct row it keeps coming up with an error! Please could you help?
Sub Latest_update()
'
' Latest_update Macro
' Loading Latest results to Latest WS
'
' Keyboard Shortcut: Ctrl+r

Dim WsL As Worksheet
Dim lastRowWsL As Long

Set WsL = Worksheets("Latest")
'Dim Sheets("DataForm") As Worksheet

lastRowWsL = WsL.Cells(Rows.Count, "B").End(xlUp).Row

Sheets("DataForm").Select
Range("D2").Select
Range("D2:D21").Select
Selection.Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Range("D1:W1").Copy
Sheets("Latest").Select

WsL.Cells("B" & lastRowWsL + 1).PasteAll ' THIS IS THE PROBLEM LINE
'Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
' Range("B2:U2").Select
'Application.CutCopyMode = False
'Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Sheets("DataForm").Select
Sheets("DataForm").Select
Range("D4:D21").Select
Selection.ClearContents
Range("D2").Select
Selection.ClearContents
End Sub
[/CODE]
[/CODE]

 

[MARK858]

Cells should be Range with that syntax

 

[PeterJohns27]

Mark, is this what you mean, as it still throws up an error albeit a different one? Peter

WsL.Range("B" & lastRowWsL + 1).PasteAll

 

[MARK858]

What does the error message state?
Also try changing PasteAll to PasteSpecial

 

[PeterJohns27]

Sorry forgot the code button

WsL.Range("B" & lastRowWsL + 1).PasteAll

Runtime error 438 Object does not support this property or method

 

[Alex Blakenburg]

Try

WsL.Range("B" & lastRowWsL + 1).PasteSpecial Paste:=xlPasteAll

 

[PeterJohns27]

Alex, yes that worked a treat many thanks.
The annoying thing is I'd already tried that but with "Cells" instead of Range Grrr!!
One for the memory bank

 

[MARK858]

Alex, yes that worked a treat many thanks.
The annoying thing is I'd already tried that but with "Cells" instead of Range Grrr!!
One for the memory bank

If you wanted to use Cells then the syntax would be

WsL.Cells(lastRowWsL + 1, "B" ).PasteSpecial

 

[Alex Blakenburg]

Since you seem to be on the way to eliminating the Select & Activate statements, I thought you might be interested in seeing what it would like without them.
Note: as Mark has pointed out the default Paste type of PasteSpecial is PasteAll so you can leave that out if you prefer.

Sub Latest_update()
'
' Latest_update Macro
' Loading Latest results to Latest WS
'
' Keyboard Shortcut: Ctrl+r

    Dim WsL As Worksheet, WsForm As Worksheet
    Dim lastRowWsL As Long
    
    Application.ScreenUpdating = False
    
    Set WsL = Worksheets("Latest")
    lastRowWsL = WsL.Cells(Rows.Count, "B").End(xlUp).Row
    
    Set WsForm = Worksheets("DataForm")
    With WsForm
        .Range("D2:D21").Copy
        .Range("D1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        .Range("D1:W1").Copy
    End With
    
    WsL.Range("B" & lastRowWsL + 1).PasteSpecial
    
    With WsForm
        .Range("D4:D21").ClearContents
        .Range("D2").ClearContents
        .Select
    End With
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[PeterJohns27]

Since you seem to be on the way to eliminating the Select & Activate statements, I thought you might be interested in seeing what it would like without them.
Note: as Mark has pointed out the default Paste type of PasteSpecial is PasteAll so you can leave that out if you prefer.

Sub Latest_update()
'
' Latest_update Macro
' Loading Latest results to Latest WS
'
' Keyboard Shortcut: Ctrl+r

    Dim WsL As Worksheet, WsForm As Worksheet
    Dim lastRowWsL As Long
   
    Application.ScreenUpdating = False
   
    Set WsL = Worksheets("Latest")
    lastRowWsL = WsL.Cells(Rows.Count, "B").End(xlUp).Row
   
    Set WsForm = Worksheets("DataForm")
    With WsForm
        .Range("D2:D21").Copy
        .Range("D1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        .Range("D1:W1").Copy
    End With
   
    WsL.Range("B" & lastRowWsL + 1).PasteSpecial
   
    With WsForm
        .Range("D4:D21").ClearContents
        .Range("D2").ClearContents
        .Select
    End With
   
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Alex, thanks for that I will definitely take your comments on the way forward. Peter