Thread: Filtering Copy and paste dinamically

Hello forum Pals
I manage to auto filter a set of data by a specific number, so the filtering is giving me the latest X number that I choose, placing it in a cell.
However, after the data is filtered, I would like to copy those filtered values and paste them in another sheet.
Because the x is dynamic and also the list increment day a day, the range is dynamic,
At this point I do not know how to do that bit in the code.
I attached the zip example
I really appreciate any help
Regards and thanks
MMF

Hi

Try this.

Sub FilterByValue()
    Dim a As String
    Dim OutSh As Worksheet
    Set OutSh = Sheets("Sheet2")
    OutSh.Cells.ClearContents 'clear out any previous data
    a = Sheet1.Range("J1")
    Sheet1.Range("A2").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:=a, Operator:=xlTop10Items
    'How can I do the Range Dynamic
    Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy Destination:=OutSh.Range("A1")
End Sub

rylo

Hi, many thanks Rylo for that bit of code, is working, but ...
I have some questions that I wonder if you can help me to understand
1.- If do not want to copy the header, if not only the values, I guest I should just past the filtered number removing the A1, but of course I don’t know hoe to do it.
2. I wonder if you have time to explain me the following line of code. I know that is for identify which is the range of the selection, but I couldn't match with the help in vba the different terms used.

Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy Destination:=OutSh.Range("A1")

Hi

1) OK, just change the range start from A1 to A2
2)

Range("A2:C"

This nominates the start of the range to copy being A2:C.....

Cells(Rows.Count, 1).End(xlUp).Row)

This is the same as going to the last cell in column A and doing a ctrl up arrow. It will select the last used cell in column A.

.SpecialCells(xlCellTypeVisible).Copy

Only grab the visible cells (ie the result of the filter) and copy them.

Destination:=OutSh.Range("A1")

This determines where the copied data has to be copied. It saves using a copy / paste approach. OutSH is a variable that has been set to the output sheet, and it is going to cell A1 in that sheet.

HTH

rylo

Many thanks, now works as I wanted.
I really appreciate the time to explain the las bit of code,