Filter and transfer fixed number to created sheet

Hello everyone
I have Sheet1 with my data in four columns (Column 4 would be "P" or "N")
and there is a template sheet ..

I need a way to filter column 4 at first with "P" and copy Template sheet for each five records and so on
Then filter column 4 with "N" and repeat the same action .. to copy template sheet for each five records

For example:
If we filtered column 4 with "P" we got 21 records : so I need to create five copied sheets and transfer each five records to a sheet >> so four sheets will contain five records completely but the fifth sheet created will contain just one record in that case

Now if we filtered column 4 by "N" we got 13 records ...so three sheets would be created : two of them will be completely of five records and the last created sheet will contain only three records ..
Hope it is clear

Can I ask what you then do with those additional sheets?

I'm always reluctant to spread one database across many sheets when the end result may be obtained in a more efficient way. I'm not saying that your requirement doesn't absolutely require additional sheets but the question should be asked.

It is desired output .. I have data and my boss need the data to be populated each five records in a sheet ..That's all

Hi,

Sometimes bosses need re-training!

Let me re-phrase. How does your boss use the information? i.e. does he really want extra sheet tabs as well as the information they contain or is he just interested in seeing the information?

If the latter then I suggest that the attached which uses a Pivot Table to give the same information is more elegant and being a PT offers a lot more flexibility to boot.

Thanks a lot Mr. Richard for this good solution ..
But the idea is not that he needs extra sheet tabs ... It is a kind of preparing these sheets for print process ..

That's exactly why a Pivot Table is so useful.

Just add another Slicer for the Group Field that I added to the database and remove the Group Field from the PT. You then have the same three items that you show on your template and all your boss needs to do is use the slicers to Filter the Pivot Table for Group and Status and then Print the PT.

One thing you could do to help him do this is add a simple Print Macro that would print the PT.

Sometimes a bit of lateral thought is needed

Thanks but I can't figure it out . I am completely ignore how to deal with pivot tables..

Originally Posted by YasserKhalil

Thanks but I can't figure it out . I am completely ignore how to deal with pivot tables..

See the attached.

The Pivot Table is currently filtered to show Group 1, i.e. the first of the 1-5 rows and Status P. Just click on other values to change the report.
I've added a macro so that the after you've entered new data on the Data Sheet the PT will automatically refresh so that the whole of the data will be used.

After you've added new data you'll need to copy the formula in column E of the Data sheet.

Thanks a lot for this wonderful solution ..
How can I create such a great pivot table myself?
What are the steps in detail please?
Thank you very much for great and wonderful solution

Hi,

To create any Pivot Table first make sure you have a regular table with column labels at the top of each column. Make sure there are no blank rows, i.e. there is a data record on every row. Now

1. Select any cell in the Data
2. From the menu choose Insert...then choose Pivot Table. I suggest you accept the default location of a new sheet.
3. From the Pivot table Field List over on the right drag and drop the filed names into one of the four areas.
4. To insert a Slicer frame allowing you to filter data, with a cell in the Pivot Table selected, click the PivotTable Tools item at the very top of the screen and then click the Insert Slicer menu item and choose the field that you want to use.

Whilst they take seconds to create you'd be advised to spend a little time getting to grips with the rich functionality they offer so that you can take full advantage. Try this site to give you some ideas
http://www.contextures.com/CreatePivotTable.html

Thank you very very much for this wonderful steps .. I learned something new
When applying the steps everything is ok and I could make similar pivot table as your ... Just one point what about these marks rounded in red ..how can I get rid of them and what are they?
Untitled.png

Hi,

Right click anywhere in the PT and choose Pivot Table Options
Select the Display tab and untick the show expand/collapse buttons

The Expand + button will show every instance of the values in the column to the right which is associated with the + button you're usingw. When you click the - sign this collapses the data and won't show anything in the column to the right. With your data it's not really relevant since you have a one to one match between Name and Marks, but if you had more than one mark for a name then you'd either see a Name and nothing in the Mark column, or a name and all the marks associated with the name depending on how you've toggled the +/- button.

Thank you very much for great help
Best Regards