Problems creating multiple reports/workbooks from masterdashboard

[michel.barca]

I've be working on a reporting macro based on Jerry Beaucaire's excellent FillOutTemplate macro.

The problem's I'm encountering (in attached simplified workboor are):
1) Dashboard selection: How can I make sure workbooks are only created for the flagged names in tab "Dashboard selection" ?

I would like reports to be created only for the names where in the tab "Dashboard selection" the "Report?" column = 1. If I use any method I know to create this selection on the Data tab (e.g. VLOOKUP) the fields where the VLOOKUP is empty/error also get parsed, so the macro returns an error.


2) Using different templates depending on dashboard: How can I get the macro to use different templates for different flags in the tab "Dashboard selection"? Everything I try to modify the existing macro gives parse errors (I'm duplicating the code for the template filling).

For example, for Name=Name1, because "Report?"=1 we need to create a report with 3 tabs:
Main-Name1 = tab "Template" filled with all fields
RepA = tab "Template-repA" filled with only the Address
Rep B = tab "Template- repB" filled with only the City
(no tab Rep C, because there is no flag in the dashboard)

3) Submacro's in template sheet: How can i get this macro to run a sub macro on the Template sheet?

For example, whenever the tab "Template_repA" gets added to a report I would like to run the macro "TestMacro" as present in the workbook. I tried adding the code to the overall macro, which works, but I would prefer to be able to "run"/"call" the seperate "TestMacro" macro from the FillOutTemplate macro, so I can have many different macro's per tab and only run them when necessary. Is this possible?

For reference I've attached the master workbook and a sample output file which it should generate (for Name1), but which I have been unable to generate automatically so far.

I've been playing around with this for a few days, but I'm afraid my excel programming skills fall short. Any help on one or more of these issues would be enormously appreciated!

[michel.barca]

By playing around in the meanwhile I've progressed somewhat on problem 2 (still stuck on the others). It's almost working, but everytime I run the macro I get a "subscript out of range error" when I try to move/save the completed sheets/workbook.

Anyone have an idea what I'm doing wrong or how i can troubleshoot it? The error is on line: ActiveWorkbook.SaveAs SavePath & x, xlNormal

The problematic part of the macro is listed below:

Please Login or Register  to view this content.

The full macro is listed here:

Please Login or Register  to view this content.

Cheers!

Michel

[michel.barca]

OK problem 1 is solved by a macro that uses a select all while standing in on of the cells of the dashboard selection with autofilter:

Please Login or Register  to view this content.

Also, problem 2 is partly solved by replacing: the save sheet command with:
ActiveWorkbook.SaveAs SavePath & x, FileFormat:=52
Apparently, Office 2010 fails when you don't explicitly and correctly specify the file format.
I'm still struggling with how to only create some tabs depending on the dashboard.

Also, problem 3 is solved by using "call [MacroName]" in the macro as long as the macro resides in the same workbook.

Anyone have any idea on (the remainder of) problem 2?

[michel.barca]

All problems solved in the attached workbook. I hope it is useful to anyone. Considering how simple the solutions are in retrospect I am somewhat disappointed no one was able to point me in the right direction.

Was my question or post not clear enough? Any feedback would be very much appreciated, so I can avoid spending 12 hours next time on something which an expert here might solve in 10 min, because my post was not clear enough or for some other reason which is not clear to me.