Add new sheet with respective list in the filter selection.

[Anbuselvam K]

Dear Excel Guru,

I need a VBA code to add a new worksheet with respect to the selected columns filtered list. The attached file has some data of rows with a data filter.
If I select any of the filters and run the code, then it should add the list of the new sheets with it is selected list.

Example
In the below snapshot I have selected one filter and it shows 4 values (M15 SCREED, M30, M40, and M50). So it has to add 4 new sheets with the worksheet name of filter list value
Sheet1 ("M15 SCREED")
Sheet2 ("M30")
Sheet3 ("M40")
Sheet4 ("M50")

Sheet2 M30 should have the rows of data for M30 ONLY. Likewise, remaining all sheets should have it is respective rows of data.



As like above if I select another column filter like the below snapshot and run the code manually, then it has to add 7 new sheets as the filters have 7 values (6, 7, 8, 9, 10, 12, and 14), then the respective rows of data to be transferred to the new sheets.



Also, all column heading should be repeated in all the new sheets irrespective of blanks if any. (QTY)
heading should be placed in row 4.

Expecting your valuable reply. Thanks in advance.

 

[fadee2]

I think this is not possible using the filter feature of excel, because the filter feature only provides a basic tool to review and analyse the data set, as seen in your screenshots. To achieve this task one has to move to VBA.

hth...

 

[Anbuselvam K]

Thanks a lot for your reply @fadee2
Okay, If the filter feature cannot achieve the desired results,

Then If I select the heading of any columns and your suggested VBA code can do the same I think.

If possible please provide the code which can add the sheet according to the selected heading columns list of values.

 

[fadee2]

If possible please provide the code which can add the sheet according to the selected heading columns list of values.

Actually, whole of your query is possible using VBA. Starting from Selecting specific items (M30, M40, M50, ....) in your dataset and creating their individual worksheets, and copying your item specific data from master sheet to each individual sheet.

I'd suggest you provide a desensitized portion of your worksheet containing your dataset to work with. This would be helpful for anyone to provide a suitable solution.

One thing though, from the screenshots you provided, looks like you are using a MAC (since I haven't used it myself), for which I am not sure, whether the VBA Code will work on MAC, in a similar fashion as it does on Windows OS, since both of these are different platforms.


hth

 

[Anbuselvam K]

Thanks for your swift response @fadee2

Actually, whole of your query is possible using VBA. Starting from Selecting specific items (M30, M40, M50, ....) in your dataset and creating their individual worksheets, and copying your item specific data from master sheet to each individual sheet.

Yes, it is possible by VBA Code.

desensitized portion of your worksheet containing your dataset to work with

Sorry, I did not understand what you are expecting.

One thing though, from the screenshots you provided, looks like you are using a MAC (since I haven't used it myself), for which I am not sure, whether the VBA Code will work on MAC, in a similar fashion as it does on Windows OS, since both of these are different platforms.

Let some MAC or Windows users supply the code.

 

[fadee2]

Sorry, I did not understand what you are expecting.

I meant to say, you should post a worksheet with part of your actual data to work with. This way, it will be easier for anyone going through the post to provide the solution.

EDIT:
my bad, missed your attached worksheet at the beginning of the post.

 

[Anbuselvam K]

In the attached sheet I have split and added all the new sheets with it is data.
Sheet1 is the source data, remaining all is the new sheet I have added manually.
Here, I added the Stations and Grades list for example.
I need a VBA Code which can add only selected headings list.

 

[fadee2]

I have attached a macro-enabled Workbook. Steps are as follows:



Give it a go and see if this helps....

 

[Anbuselvam K]

It is working excellent. Thanks a ton, @fadee2 for your time and the solution.

 

[fadee2]

You are welcome.... glad to help...