Paste data from another workbook excel vba, error: Paste method of Worksheet class failed

Question

I'm trying to copy a set of data from a workbook to another workbook, let's say I'm going to copy data from Book A to Book B. First, I go to Book A copy the list, then go to Book B to run below copydata macro(I want to paste the data onto the new sheet before go back to the previous sheet in Book B.)

Sub copydata()

Dim lstviewpg As String
lstviewpg = ActiveSheet.Name
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "newsheet"
Sheets("newsheet").Activate
ActiveSheet.Paste
Sheets(lstviewpg).Activate

End Sub

The macro failed at line 6 due to Paste method of Worksheet class failed. Anyone got any idea how to fix it?

ActiveSheet.Paste

Answer 1

Paste method of Worksheet class failed error will also be produced if there is nothing on the clipboard i.e. clipboard is empty. So to confirm that clipboard is not empty, right click in any cell on the sheet and if the Paste is greyed out that means the clipboard is empty.

Answer 2

You have to use PasteSpecial()

Moreover you can simplify a little:

Sub copydata()

    Dim lstviewpg As String
    lstviewpg = ActiveSheet.Name

    With Sheets.Add(After:=Sheets(Sheets.Count)) '<--| add a new sheet and reference it
        .Name = "newsheet" '<--| set the name of referenced sheet
        .PasteSpecial '<--| paste data
    End With
    Sheets(lstviewpg).Activate

End Sub