Microsoft Visual Basic error '1004': PasteSpecial method of range class failed

Question

This is a document that someone before me created and is barely used. So yes, I could rewrite it, but I wanted to see if there was a way to make this one work.

Basically I am removing old data, refreshing the data, and pasting the new data into A4. Doing research, one person said that this error occurs because there is nothing in the clipboard, which is not correct in my case because when I close it tells me I have a lot of data on the clipboard.

It was working fine up to last week.

The error occurs after I remove the data refresh it and the want to move the new data to correct report:

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Here is the code:

 Sub move_data()
'
' move_data Macro
'

'
    Sheets("Table").Select
    Range("A4").Select
    Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select

    Selection.Copy
    Sheets("Call List").Select
    Range("A4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Call Date").Select
    Range("C20").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Call List").Select
    Call test
End Sub

Answer 1

Here is the whole code.

Sub remove_old_data()
'
' remove_old_data Macro
'

'
    Sheets("Call List").Select
    Range("A4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Sheets("Macro Page").Select

End Sub
Sub move_data()
'
' move_data Macro
'

'
    Sheets("Table").Select
    Range("A4").Select
    Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select

    Selection.Copy
    Sheets("Call List").Select
    Range("A4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Call Date").Select
    Range("C20").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Call List").Select
    Call test
End Sub
Sub refresh_data()
'
' refresh_data Macro
'

'
    Sheets("DL Data").Select
    Range("B10").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Table").Select
    Range("B23").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    Sheets("Macro Page").Select
End Sub
Sub test()
'
' test Macro
'

'
    ActiveWorkbook.Worksheets("Call List").ListObjects("Table3").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Call List").ListObjects("Table3").Sort.SortFields. _
        Add Key:=Range("Table3[Balance]"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Call List").ListObjects("Table3").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Answer 2

Try eliminating all of the select and tab switching like this:

 Sub move_data()
'
' move_data Macro
'

'
    With ThisWorkbook

        With .Sheets("Table").Range("A4")
            .Resize(.End(xlDown).Offset(-1, 0).Row - .Row, 1).Copy
        End With

        .Sheets("Call List").Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

        .Sheets("Call Date").ListObject.QueryTable.Refresh BackgroundQuery:=False

        .Sheets("Call List").Select

    End With

    Call test

End Sub