What's wrong with my Paste Macro?

[BMil8]

Hello,

Can anybody tell me why I'm getting the "Paste Method of Worksheet Class Failed" error message? I'm trying to filter column E for non-blanks and then pasting them after the last cell with data in Column D.

ActiveSheet.Range("Competitor").AutoFilter Field:=5, Criteria1:="<>"
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveSheet.Range("Competitor").AutoFilter Field:=5
Range("D" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste

Thank you,
Brian

 

[gsistek]

You need to reference the range in which you are pasting. replace the last two lines with
Range("D" & Rows.Count).End(xlUp).Offset(1).Paste

 

[BMil8]

Now I'm getting an error that says "Object does not support this property or method".

 

[dmt32]

not tested but see if this goes in right direction for you.
adjust code as required.

Sub CopyFilterData()
    Dim rng As Range
    Dim rng2 As Range
    Dim LastRow As Long
    Dim ws1 As Worksheet
    
    'worksheet you are filtering
    'change name as required
    Set ws1 = Worksheets("Sheet1")
    
    On Error GoTo exitprog
    ws1.Range("Competitor").AutoFilter _
            Field:=5, _
            Criteria1:="<>", _
            VisibleDropDown:=False
    Set rng = ws1.AutoFilter.Range
    Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
    Set rng2 = rng.SpecialCells(xlCellTypeVisible)
    If Not rng2 Is Nothing Then
        With ws1
        LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row + 1
            rng2.Copy Destination:=.Range("D" & LastRow)
        End With
    End If
    rng.AutoFilter
exitprog:
    If Err > 0 Then
        MsgBox (Error(Err))
        Err.Clear
    End If
End Sub

 

[BMil8]

So this code filters the data correctly, but copies columns A:E instead of just E, and it pastes it 10 rows before the final cell of data in row D instead of in the next empty cell.

 

[dmt32]

So this code filters the data correctly, but copies columns A:E instead of just E, and it pastes it 10 rows before the final cell of data in row D instead of in the next empty cell.

Tone of your response sounds somewhat ungrateful for the help offered.</SPAN>
I did state code was untested & that you will need to adjust code as required.</SPAN>
As with all that offer help on this board – solutions are a best guess what OP is seeking not always a fully working solution.</SPAN>

Perhaps this would be nearer to what you are seeking but again, you will need to adjust or adapt to fit your need.

</SPAN>
Sub CopyFilterData()
    Dim rng As Range
    Dim LastRow As Long
    Dim ws1 As Worksheet
    'worksheet you are filtering
    'change name as required
    Set ws1 = Worksheets("Sheet1")
    LastRow = ws1.Cells(ws1.Rows.Count, "D").End(xlUp).Row + 1
    On Error GoTo exitprog
    ws1.Range("E1").AutoFilter _
            Field:=5, _
            Criteria1:="<>", _
            VisibleDropDown:=False
    Set rng = Range(ws1.Range("E2"), ws1.Cells(ws1.Rows.Count, "E").End(xlUp))
    Set rng = rng.SpecialCells(xlCellTypeVisible)
    If Not rng Is Nothing Then
        rng.Copy Destination:=ws1.Range("D" & LastRow)
    End If
    rng.AutoFilter
exitprog:
    If Err > 0 Then
        MsgBox (Error(Err))
        Err.Clear
    End If
End Sub

 

[BMil8]

Tone of your response sounds somewhat ungrateful for the help offered.
I did state code was untested & that you will need to adjust code as required.
As with all that offer help on this board – solutions are a best guess what OP is seeking not always a fully working solution.



[/code]

I don't appreciate your assumption. Did I come right out and say I am ungrateful? Go ahead and look at all of my other threads starting back in 2010. I've always told people how great they are for solving my problems. The only thing I did here was continue a discussion and explain to you what the code did in hopes you could help me fix it. I usually say thank you at the end of a conversation, not in every single post.

Please do not try to help me any further.