Use Macro to add Sheet (template) and consolidate at the end

RgotG

New Member
Joined
Mar 16, 2018
Messages
9
Hey I'm trying to semi automate a recurring process.

There are about 4-6 different cases that can occur in any combination. scenario 1: 5 times case A and one time case B; Scenario 2: 2 times case A and 3 times Case B. You get want I'm saying. For each case (A,B,C,D etc) I have a template that has certain assumption fields that need to be filled in by the user. However given that there are so many different possibilities I would like to have a master workbook where on sheet 1 there are 5 or 6 different buttons. Each designated to insert a different case template from the template folder.

I want to be able to click the button and the sheet gets inserted. Preferably prompting me for a name. (I also wanted to use a UserForm to fill in the case assumptions, but I use Excel on Mac version 2016 - not available... If someone knows a usable workaround - that would be awesome.

Next step: Assuming I have a couple case templates and filled in the assumptions for each template. Now I want to consolidate all of them. 1 sheet that gives me certain data of each case template. E.g. if case A has Awesomeness level of 2 in year 1 and 3 in year 2 and 7 in year 3 then I want the code to copy that specific row and display it as followed:
Case A 2/ 3 / 7
It should also recognize if two case templates are of the same case/ template type and have them right below each other so a sub total of awesomeness can be calculated for those cases.

So far I kinda get that I can use Sheets.Add to add a sheet and that I should be able to define a certain template. Also do the templates need to be macro enabled?

As you can tell I'm new to this and any hint or help would be highly appreciated :)
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I solved the first part :) Have two buttons now inserting the appropriate case sheets.
Prompting for a name will be the next challenge, followed by the big one: consolidating the case sheets - if someone has an idea, that would be amazing
 
Upvote 0

Forum statistics

Threads
1,223,391
Messages
6,171,824
Members
452,426
Latest member
cmachael

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