How would you set this up - building from scratch

TheRedCardinal

Board Regular
Joined
Jul 11, 2019
Messages
241
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
 
Upvote 0
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?
 
Upvote 0
I would do the calculations like this:
Code:
' 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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top