How would you set this up - building from scratch

[TheRedCardinal]

I'm building a Macro for a project. It's going really well but quite a learning curve.

The project has about 20 outputs. The first steps for all 20 are the same, but at a certain point the process diverges for each, mainly in terms of producing a Pivot Table with different fields (but all from the same source data), and then leading to a final output - either a report, or a CSV file.

What I realised was if I write those common scripts, then save each output model as a different spreadsheet, then I can't deliver updates to them all without editing 20 spreadsheets.

I have read bits about macros being available across multiple workbooks but haven't quite figured out yet how that works, especially as several team members need access to the files.

I was thinking of having my final report templates all saved in a separate workbook and using 3 named cells to identify which one to copy over into the active workbook; but I Still have issues around the pivot table and formatting.

Any suggestions?

 

[offthelip]

Can you not generate a worksheet that is the intermediate state, after the common first steps and only branch out to twenty different sheets where the model actually differs

 

[TheRedCardinal]

Yes that is what I would like to do I think.

But I am unclear how to do the branching? It is clear i need one central piece of code for say steps 1 -30.
But then steps 31 - 45 would be different.

What is the best way to combine the 2?

 

[offthelip]

I would do the calculations like this:

' define varaibles that you want to use in more than one subroutine here
' this will mean the scope of the variables is the module so the variable isn't destroyed when the subroutine finishes
Sub control()
Call Generalcalcs
Call pivot1
Call pivot2
Call pivot3
' etc etc
End Sub


Sub Generalcalcs()
' put the common calculations in here
End Sub
Sub pivot1()
' put the code for the first pivot table in here
End Sub
Sub pivot2()
' put the code for the 2nd pivot table in here
End Sub
Sub pivot3()
' put the code for the 3rd pivot table in here
End Sub

 

[offthelip]

I forgot to answer the question about how to manage it all. The way I would do it is to have one workbook with all the code in it and all the "templates" that you need. Then you generate the latest worksheet, and then copy it to a new workbook and save the new workbook with the latest name ( all using VBA) . this allows you to have everything in one master workbook .
I did a project like this that catered for the user to generate their own template by selecting what variables they wanted in the report, and even change the layout and the format of the templates. This was very easy to maintain, because I just issued one new workbook with a new version whenever an update was needed