Filter Specific data and copy paste to new worksheet

If I'm not mistaken to get what you mean, it is something like this :
1. Sheet2 column B starts from row 8 to row whatever > row 8 contains the criteria
2. Sheet1 column E need to be filtered with the criteria/s from point-1
3. After already filtered, copy the whole filtered data to Sheet Output starts from cell B4.

My way is like this :

Please Login or Register  to view this content.

2020-05-20_17-27-26.gif

The code is assuming that your data table is just like I saw in your Sheet1 workbook, it has no blank row in between.
And also the criteria range in Sheet2 column B has no blank row as I saw in your Sheet2 workbook.

Originally Posted by SARAN89

Can you please help to make small the below changes.

I will try my best.

Sheet2 column B: This is not fixed one,
can you provide option to select the rows (selection of Range)

Do you mean that the criteria will be based on the user's selection on Sheet2 ?
(so it doesn't matter on what column/row where the criteria text are)

("B5", .Range("B5")= This is also not fixed one,
some times i need to select Column C, can you change to modify the code to "Selection of Column)
or VB input of Column name and row number (Ex B5)

I am sorry I didn't get what you mean, Saran89.

The line :

Set TblRng = .Range("B5", .Range("B5").End(xlToRight).End(xlDown).Offset(0, 1))

is for setting the table range.

Do you mean that the starting table range is never fixed, so for example there is a possibility
that the table range starts from cell B5 to G100 ... but at another situation
the table range starts from cell A1 to D200 ... and another situation it might happen
where the table range starts from cell K4 to Z300, and so on.

some times i need to select Column C

Or do you mean that the column to be filtered is not fixed ?
Sometimes maybe the criteria text from Sheet2 are in Sheet1 column E (as this is the example in your workbook)...
but sometimes maybe the criteria text from Sheet2 are in Sheet1 column C, etc.

So, for example, you will select column C to be filtered out,
because the text criteria in Sheet2 are all dates which resides in Sheet1 column C.
That's why the column to be filtered is column C in Sheet1, not column D.

So, the column to be filtered is never fixed.
It might be whatever column of the table range in Sheet1 to be filtered.

Originally Posted by SARAN89

Do you mean that the criteria will be based on the user's selection on Sheet2 ?
(so it doesn't matter on what column/row where the criteria text are)

Yes by User Selection within work book its not fixed Sheet2

So,
A. it's possible the text contains the criteria are in another Sheet ya ? Not just Sheet2 ?
Or
B. do you mean that the Sheet where the criteria text resides is fixed,
but the name of the sheet is definitely not Sheet2 ?

Yes you are correct that's why i am asking to select column.
if you provide VB input box i will give input of ex B5, A1, K4, means this will be me header to make filter.

Ok... so, to the end-left from user selection in Sheet1 ---> this is the starting column of the table range.
And to the end-right from the user selection in Sheet1 ---> this is the end column of the table range.

For example :
the user choose cell H20 ...
So, cell H20 is the header to be filtered...
And so the starting table range column could be anything,
maybe it's cell A20 or cell B20 or C20 or D20 or E20 or F20 or even H20 itself.
And the last column of the table range also could be anything, maybe it's cell I20, J20, K20, or even maybe cell AA20.

Am I correct, Saran ?

Please try this :

Please Login or Register  to view this content.

The code is using a helper column in Sheet 2, which is column AA.
So please make sure that there is no data at all in column AA Sheet2.

The code assumes that the text of the criterias will be in Sheet2,
so before the code ask the user to select the criteria, the code activate Sheet2.
After the user select the criteria range, it copies the range to cell AA1 in Sheet2.

Then the code activate Sheet1, then ask the user to select the HEADER CELL to be filtered.
Based on the user selection, the code locate the starting column and the ending column of the table range.
The code will fail if there is a column in your table header which has no value.
The code also will fail if in the last column of the table range - there is a row with no value.

Originally Posted by SARAN89

Hi


Yes the critiriea will be in any worksheet on the same workbook. Its not just sheet2.


And Sheet1 also not specific. it will be active worksheet.

In that case,
please ignore test2 macro.

Try this one :

Please Login or Register  to view this content.

This code won't activate sheet for the user.
So, the user need to select the sheet, then choose the criteria range.
Once the user click OK in the input box, it automatically comes back to the previous active sheet (in this case Sheet1).

So please remember that the user has to start in a sheet where the data to be filtered resides.

Originally Posted by SARAN89

I am trying to create the "Output" sheet from the code itself, hence added the below code. after selecting range its goes to output tab. may be i added in wrong place.

Please Login or Register  to view this content.

It seems you are correct putting the new line before the "Set Dest".

Please remember, the code is using a column helper right after the last column of the table range.
So please make sure that there will never exist any important value in any row of the column after the last column of the table range.

Originally Posted by SARAN89

Hi karmapala.

Test4 working as expected. Thanks for your help.

You're welcome. Glad I can help

Originally Posted by ayyappan80

I am trying to make small changes on this code on the below line, like inputting text or number or Range of Cell

Please Login or Register  to view this content.

But its prompting only for Range of Cell. seeking experts help.....

I think if you use type:=8, then this for a range.
If you want to put a text, then the code is without the "Set" and without the type.
Please search on the internet about inputbox type.

Or read this : link