Macro to filter data into different sheets

Question

I have data with following headers: Sr. No,Audit Parameter, Process, Sub Process, Function & Weightage. There are around 200 rows of data. I want a macro which will filter the data on function & seperate them into different sheets with the following headers

• Sr. No, Process, Audit Parameter. The first row should have the Function (the 3 cells over the headers - Sr. No, Process & AUdit Parameter merged). The Sr. No. should be reset for each sheet with serial no starting from 1.  The rows with common process (2nd column of the filtered sheet) should be merged (horizontal centering - vertical centering ). The worksheet should be named by the function on which the data is filtered.

In case these sheets are already existing in the workbook, the same should be deleted & then new worksheets shoould be generated. However if there are no previous worksheets, no error should be reported

Answer 1

UniqueFunction should hold a list of Unique functions from your database (column E)

the code is looping through this list and adding blank worksheets for each one.  As it does that, it attempts to name the sheet using the name of the function.  So if you are getting an error, the value of  UniqueFunction(c)  is not a valid sheet name.  This is a data problem.  So you would have to check what the values are . 

you could add a line

msgbox c & ": ->" & uniqueFunction(c) & "<-"

destSh.Name = UniqueFunction(c)

Then you can see what it is trying to name the sheet (even detect if UniqueFunction(c) is empty).

--

Regards,

Tom Ogilvy

Answer 2

The macro recorder is your friend

Data>filter>autofilter>filter on Process>copy to new sheet>name it

However, you really don't need and probably shouldn't use a separate sheet for each when filtering gives you the same or better flexibility. I strongly suggest doing it that way.

---

Don Guillett MVP Excel SalesAid Software *** Email address is removed for privacy ***

Answer 3

My master data (Sheet name : Audit Tool) looks like this

Sr. No.	Audit Parameter	Process	Sub Process	Function	Weightage
1	Cash reconciliation done	Cash Management	Cashiering	Finance	5
2	Assortment planning done	Inv Mgmt	Store Assortment	B&M	3
3	Banking done	Cash Management	Cashiering	Finance	2
4	CSD neat & clean	Floor Process	Hygiene & Cleaning	Ops	4
5	Pricing study done	Inv Mgmt	Competion Analysis	B&M	4
6	Stock availability	SCM	Logistics	B&M	1
7	SKU range & depth	SCM	Planogram	B&M	2

The report i want should be on different sheets for each function (in the above illustration 3 different worksheets namely Finance, B&M & Ops). I want the sheets to be created when i run a macro. Each sheet should look like

B&M
Sr. No.	Process	Audit Parameter
1	Inv Mgmt	Assortment planning done
2	Pricing study done
3	SCM	Stock Availability
4	SKU Range & depth

This should get generated in a new sheet & the new sheet should be named as B&M. Ditto for all the functions that i have in the master table.  The next time i run the same macro, it should delete any existing function worksheets  create new. If the worksheets do not exist (the first time when i run the macro), it simply generates the worksheets.

Second step - i want to calculate scores in the 4 col for each process-audit parameter point. I know the formula but how do i ensure that it is run in only as many rows as the data is present (above case 4 rows)

Plz help

Nikhil

Answer 4

nikhil_s_k

the sample code at this link should get you started:

http://www.rondebruin.nl/copy5.htm

You know more about your data and needs than anyone here. 

--

Regards,

Tom Ogilvy

Answer 5

Please provide some sample data and an example of what a filtered sheet should look like.

/Per