Copy and Paste, paste to list end

[Danny]

Hi People

Quick question, I want to copy a list of number from one work sheet and paste them into another work sheet at the end of the list. I dont want the pasted values to overwrite anything in my column.

I currently have the following, it wants to paste the values at the top of my list:

ActiveSheet.Range("$A$3:$EJ$791").AutoFilter Field:=24, Criteria1:= _
        "CON EMEA"
    ActiveSheet.Range("$A$3:$EJ$791").AutoFilter Field:=9, Criteria1:="#N/A"
   
    Range("O4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Department_Project").Select
    Sheets("Department_Project").Range("A724").End(xlUp).Offset(1, 0).PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Any help would be greatly appreciated.

Thank you
Danny

 

[SirJB7]

Hi, Danny!

Give a look at this file:
https://dl.dropboxusercontent.com/u...e to list end (for Danny at chandoo.org).xlsm

This is the code and works fine:

Option Explicit

Sub DannyHadALittleLamb()
    ActiveSheet.Range("$A$3:$EJ$791").AutoFilter Field:=24, Criteria1:="CON EMEA"
    ActiveSheet.Range("$A$3:$EJ$791").AutoFilter Field:=9, Criteria1:="#N/A"
 
    Range("O4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Department_Project").Select
    Sheets("Department_Project").Range("A724").End(xlUp).Offset(1, 0).PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Regards!

 

[Smallman]

Danny

As you are already on the Activesheet there is no need to use this in the syntax. There is also no need to select anything in your code. From your coding this should suffice.

Sub MoveData()
    Range("A3:EJ791").AutoFilter 24, "CON EMEA"
    Range("A3:EJ791").AutoFilter 9, "#N/A"
    Range("O4:O791").Copy
    Sheets("Department_Project").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial xlPasteValues
End Sub

Take care

Smallman

 

[Danny]

Smallman and SirJB7

Thank you for your comments and support. Answered my question perfectly and it has worked perfectly.

Much appreciated
Danny

 

[SirJB7]

Hi, Danny!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!