I am trying to create a button that will filter a table, select and copy data from cell D6 and only the visible data from a filtered table with a range D13 to D9999 to the clipboard so that I can use the information to paste it anywhere else such as Word, notepad, one note, etc using CTRL V. I have written this code but nothing gets pasted when I go CTRL V. I think it has to do with the un-filtering that I do in the end. But that is important. How do I get this working?

Dim ws As Worksheet
Set ws = Worksheets("Cost")
ws.Range("D13:D9999").AutoFilter field:=1, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor
Range("D6","D13:D9999").Select
Selection.COPY
ws.Range("D13:D9999").AutoFilter
End Sub

Been researching it for a while But I can't seem to find anything that will work. Thanks.

CodePudding user response：

Qucik and dirty solution is to

• remove the AutoFilter at the end. Infact, make sure that .Copy is the last command in the code - this action will keep the Application.CutCopyMode which retains the copied text in clipboard until further action.
• And only copy a single Range i.e., remove D6 from copy.

Dim ws As Worksheet
Set ws = Worksheets("Cost")
ws.Range("D13:D9999").AutoFilter field:=1, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor
Range("D13:D9999").Select
Selection.Copy

End Sub

CodePudding user response：

If you want to copy the visible cells only after applying the filter, something like the following: (note I avoid using Select)

Set r1 = [d6]

'filter the range
Set r2 = Range("d10:d20")

r2.AutoFilter field:=1, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor

Range(r1, r2.SpecialCells(xlCellTypeVisible)).Copy

You'll then have to execute your Paste before you turn off the autofilter.