Filtering and generating new tabs based on Unique values based in column

Dear ,


For understanding this project ,please see the attached workbook's......

The first one "Input "see the tab called final ,

Now ,please open the spreadsheet input 1 and see tab Final 2.......

The only difference between Final and final 2 is that the blank rows (Note it does have value in cell H -But for my purpose any row with value just in H is a blank row and needs to be deleted and new tab needs to be prepared exactly like final 1 ......

Now in final 2,you will see that i fill blank entries in column E as NOT FOUND ,

Now see attached sheet DBC project filter Output sheet ....you will note that based on unique values in column E ,seperate tabs are created -i did use datapig explosion for the purpose but wish that i can have a macro for the purpose ...

Also you will note that the attached macro already has a subroutine defined which generates the final sheet based on content of sheet 1 and sheet 2 .......

(is it possible that you make this macro and call oit from the first macro so that on single click of the first subroutine ,all the filtering and then further tabbing takes place )
Anyways ,just making a mcro for the thread will solve my worry ,since i can then easily call the macro from the first macro myself ,

Thanks in advance ,

Regards ,


Amlan Dutta

Hi Amlan, try this

Please Login or Register  to view this content.

Also try this line instead of 'Call notfound'

Please Login or Register  to view this content.

You my friend ,is a pure genius !

This works like magic !

But the code is very difficult to understand for a newcomer like me ,but is a brilliant piece of work ,trust me ,what you have done using your code simply is beyond my mind to do manually ,

But i want to be in a through position to understand whats happening here ,

an you please explain what's happening here ,

Please Login or Register  to view this content.

what is the compare mode and what are the keys?

also what is this line doing ?

Please Login or Register  to view this content.

Find the input and output file attached !

This is just perfect ,

I also want to know whether you can increase the column width in the output file for all columns to 40 pixels or something which will help us see clearly all contents ,you will see that if the column width is less than the conetnt appears as ##### and we have to drag the column width ...can the macro handle to arrange the column width so as to make it visible ,

I will take a little assistance of yourself for understanding the code ,

P.S- I don't know how to use the alternative you gave in place of Call NOTFOUND ,i mean i am clueless as to where i should be putting it !

The entire code which i used for managing the input and output files is as follows ,

Please Login or Register  to view this content.

Humble regards ,


Amlan Dutta

Originally Posted by amlan009

...I also want to know whether you can increase the column width in the output file for all columns ...

Try changing these lines

Please Login or Register  to view this content.

Dictionary is an object that can contain only unique values ​​of keys.
x = .keys is an array of dictionary keys, i.e. sheet names.
Expression of If Not Evaluate("ISREF('" & x(i, 1) & "'!A1)") Then examines whether there is a worksheet named x(i, 1).

You can write a line

Please Login or Register  to view this content.

instead of a line

Please Login or Register  to view this content.

in your code.