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

Just FYI - It's very highly recommended to [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251). This whole macro could be reduced to about 3 lines. — BruceWayne, Sep 13 '18 at 17:28

score 1 · Answer 1 · answered Sep 13 '18 at 17:41

1

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

answered Sep 13 '18 at 17:41

Valon Miller

1,156
5
9

Deleted my answer since this is of the same ilk and more streamlined, although I wouldn't do copy and pasting of any sort, would it be best to directly transfer? – jamheadart Sep 13 '18 at 17:43
@jamheadart I left it as copy/paste because I don't actually know what the data looks like and therefore don't want to create additional assumptions. – Valon Miller Sep 13 '18 at 18:01
I am still running into the same error on this part. .Sheets("Call List").Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False It would probably be best to transfer it directly however my boss does not like change and doesnt understand why it worked before and now doesn't. – CCTD Inquiry Sep 13 '18 at 18:02
Microsoft Visual Basic error '1004': PasteSpecial method of range class failed – CCTD Inquiry Sep 13 '18 at 18:03
@CCTDInquiry I'm not sure that there is enough information provided to resolve this issue. If the above code did not work, then there may be other factors in your workbook that we cannot see that are affecting the execution of this sub. – Valon Miller Sep 13 '18 at 18:04
1

That error could mean it's trying to paste into a table that has a filter on? - or trying to paste over some headers in a table? – jamheadart Sep 13 '18 at 18:10
Thank you Yes a filter was on and that is why it didn't work – CCTD Inquiry Sep 13 '18 at 18:28
@jamheadart good call, I had a feeling it was something like this, that we just didn't have enough visibility to. – Valon Miller Sep 13 '18 at 18:31

score 0 · Answer 2 · answered Sep 13 '18 at 18:13

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

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

2 Answers2