PDA

View Full Version : Solved: Create new tab based on list



MRichmond
02-10-2012, 03:07 AM
Hello to you gods of VBA

I'm looking for a two part help on this project please

I have a workbook, in a portion of this deals with scores, and the displaying of them.

Part one

There is the main data tab "Audit Scores" and what I need to do, is check for unique values in column B, and then check to see if there already exists a tab for that name, and if not I need it to create that tab, and copy the macros from another of the agent tabs onto this tab (these macros unhide columns, advance filter the Audit scores based on agent name, then rehide the columns).

Part two
There are column graphs covering the data on the agent tab, but i need these to resize automatically based on tne number of rows returned.

I have put my macro's on the worksheet_activate action as there will be otehr sheets that dont need to be touched, and to be honest I'm not sure how I would loop a macro to go through only the required worksheets (column B in "Audit Scores").

Please see sample workbook which may make it easier to decipher my garbled request.

So if anyone can point me in the rgiht direction I would be most grateful.

Bob Phillips
02-10-2012, 03:47 AM
Rather than have all of the same code in each worksheet module, you would be better to have a workbook sheet activate event that tests the worksheet name to be of the required format .

MRichmond
02-10-2012, 06:39 AM
Hi XLD,

thanks for responding.

I'm with you, it probably would be easier to have the code once, and then loop through the workbook, but my lack of knowledge & experience means I dont have the necessary skills to be able to do that, hence the worksheet codes.

I'm always open to suggestions if there is a better way, this does sound like a better way and if you could possibly supply a bit of code to achieve that then i would be most grateful.

Bob Phillips
02-10-2012, 06:51 AM
I did try and work it through in the workbook, but the advanced filter did not work. Give me a bit more time.

MRichmond
02-10-2012, 06:56 AM
Take as much time as you need, before yesterday I didnt even know how to use advanced filters let alone using it with VBA. :yes

Bob Phillips
02-10-2012, 08:02 AM
How about this?

MRichmond
02-10-2012, 08:25 AM
Thanks XLD,

just what I needed.