0

I'm new to VBA and I'm facing some problems with the code below:

It just need to find all the cells with "Add" on column DE and paste the names which is on the left column DD to the last blank cell on column A. The macro runs ok until the paste line. I don't know if there is another way around it.

ActiveSheet.Range("$DD$16:$DE$200").AutoFilter Field:=109,         Criteria1:="Add"
Range("DD16").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

'unfilter Macro
ActiveSheet.Range("$A$13:$DE$133").AutoFilter Field:=109

'Paste names marked as "Add"
Range("A14").Select
ActiveCell.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Dark Seer
  • 7
  • 4
  • 1
    I think [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) might be of use. – Miles Fett Oct 02 '19 at 17:54
  • It think `.Copy` and `.Paste` have to be consecutive lines in your code... so the way you have it above, cancelling the filter also cancels the effects of `.Copy`, therefore nothing to `.Paste` – Andras Oct 02 '19 at 17:55
  • yepp, as @MilesFett suggested the proper way to get it done is going to involve replacing `Selection, Active...` – Andras Oct 02 '19 at 17:58
  • Thanks for the tip @MilesFett , I've read the article but I'm trying to think of a way to replace the Selection to select only filtered cells with value = "Add" but I Can't set a range because it won't Always be the same, there might be more or less data – Dark Seer Oct 02 '19 at 18:37
  • 1
    After the filter action, you would select column DD, to the lastrow, and use something like this... `Range("DD16:DD" & lastrow).SpecialCells(xlCellTypeVisible).Copy` – GMalc Oct 02 '19 at 18:49
  • @YKC you could also try to find the values in a range using the Range.Find("add") function. Then using an offset when the value is found to get to the right cells. – Walter Kloosterboer Oct 03 '19 at 08:31

0 Answers0