Filtering out required data

[Tom22]

Hi,

I have file where n number data is given. on monthly basis I receive data but I need only selected amount of it.

Let's say we have data from A to Z but I want data for only B<D<R<T<Y.

what I can think of code of filtering the data and then copy paste in another file.

Can any one suggest somethinng for this, where I can filter out every thing in one go and cope paste in another tab.

Attaching one sample book.

Thanks

 

[Marc L]

Hi,​

​

could be done easily with an advanced filter if the workbook is smart enough​

as in the 'Raw file' tab the cell P3 must have the same content as the header D2 …​

Then copy the headers you need to the 'Final Data' tab.​

​

So with a smart workbook you can operate manually by selecting the headers row in the 'Final Data",​

calling the advanced filter from the Data menu then just well setup its parameters - at kid level -​

as the Action must be Copy to another location, indicate the List range from the 'Raw file'​

- use the icon at right of the field - like the Criteria range as well​

and for the destination range Copy to it's just the 'Final Data' headers row, that's it !​

​

If really a VBA procedure is necessary, just reading the VBA help for the AdvancedFilter method​

as it needs a single codeline ! Or just activating the Macro Recorder before operating manually …​

​

Introduction to Excel Advanced Filters - What are they & How to use them

Excel's Advanced Filters can filter a list based on a variety of conditions and copy the values elsewhere too. Learn about Excel Advanced Filters & How to use them. In this example, we will show you how to set up advanced filters so that you can filter data meeting multiple criteria. Also, you...

chandoo.org

​

 

[Tom22]

Thanks MarcL...I don't know excel has this functionality....thanks once again for let me know

 

[rahulshewale1]

hi @Tom22,

See if it ok ?

Regard
Rahul Shewale

 

[Marc L]

rahulshewale1,​

​

as a reminder, according to VBA help and post #2 link, using an advanced filter any VBA procedure needs only a single codeline !​

Meaning all your codelines can be replaced by an unique codeline …​

Using a filter as you did needs less than 10 codelines (as the lesser is 4) without any variable just directly playing with objects.​

​

Another point as your procedure copies the button : it's not a concern when the destination sheet is in the same workbook​

- except each time you launch it a new button is pasted over the previous button(s) ‼ -​

but it could raise an issue if on another workbook if you try to use the button …​

​

 

[Marc L]

According to rahulshewale1 post #4 attachment :​

• The same filter way with only 4 codelines :

Sub Demo000()
    Sheet2.UsedRange.Clear
    Sheet1.[A1].CurrentRegion.Offset(1).AutoFilter 2, Filter(Application.Transpose(Sheet3.UsedRange), ""), xlFilterValues
    Sheet1.[A1].CurrentRegion.Copy Sheet2.[A1]
    Sheet1.[A1].AutoFilter
End Sub

​

To avoid the filter arrows flashing the procedure can be rewritten in a clean way with few more codelines :​

Sub Demo00()
         Application.ScreenUpdating = False
         Sheet2.UsedRange.Clear
    With Sheet1.[A1].CurrentRegion
        .Offset(1).AutoFilter 2, Filter(Application.Transpose(Sheet3.UsedRange), ""), xlFilterValues
        .Copy Sheet2.[A1]
        .AutoFilter
    End With
         Application.ScreenUpdating = True
End Sub

​

• As here the effcient way is the advanced filter, the one to follow :

Private Sub Demo01()
    Sheet1.[A1].CurrentRegion.Offset(1).AdvancedFilter xlFilterCopy, Sheet3.UsedRange, Sheet2.[A2:J2]
End Sub

-- ★ --
​

According to Tom22 initial attachment, once as I wrote​

« in the 'Raw file' tab the cell P3 must have the same content as the header D2 » :​

Sub Demo1()
    Sheet1.[C1].CurrentRegion.Offset(1).AdvancedFilter xlFilterCopy, Sheet1.[P3].CurrentRegion, Sheet2.[C2:L2]
End Sub