Macro to create a new sheet and add from previous sheet

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
655
Good Afternoon-

I'm trying to figure a code to go through the following processes:

I'd like to open Excel- Initial sheet is the Master Template-
1. A dialog box opens with a series of inputs (They establish the "constants" in the formulas)
2. Then Either Sheet 1 "Noon" or the Totals "Arrival" sheet is able to be selected and is created- or Modify button (see below for what it does)
a. If Sheet 1 is created, all of the formulas for sheet 1 can be added and formulated.
b. If Totals is created, all of the formulas for totals can be added and formulated.
3. The sheet is "saveas" (I have a macro for this) into a location- name from Sheet "Ports"
4. Next time excel is opened, a new dialog box opens and asks for "Noon" (Sheet2) or "Arrival" (Totals) or Modify (allows for changes to previous sheets, "Notes,"Ports," and "Developer" Sheets
a. Sheet 2 takes all numbers from sheet 1 formulas and applies them in sheet 2 formulas
b. Total takes all numbers from sheet 1 formulas and applies them in totals formulas
c. This sheet will be saved
5. Next time excel is opened, same options as Sheet 2 with options for "Noon" (Now sheet3), or "Arrival" (totals).
6. this process continues on, ideally infinitely, and "Arrival" (Totals) sheet is the addition of all previous "Noon" sheets plus it's own numbers.

I currently have this working with formulas and a lot of "If" statements but it goes for already created sheets- I have "Noon" to "Noon 10" and "Arrival." This is a report put in each day- Currently there's a section on the "Noon" (since that's the first sheet to open) that I put all of the constants in, then each day I add recorded numbers and some of them are just put into cells (for the printable area), so are applied in formulas- example: One cell I input the total mileage and it tells me the mileage gone since the previous noon. Some of this info is put on the "Arrival" form which accounts for the time from the last noon (data inputted) to the conclusion of the voyage and also accounts for some data from the entire voyage. Then I have three "data" sheets that have static numbers used in the sheets- like dropdown data and such. Happy to share the workbook for anyone curious.

Anyway- is there a way to create a macro that generates a new "Noon" and will tell it to apply the formulas and seek data from the previous Noon- I know how I could do it with a new macro for every single noon but I don't know how to do it where it could generate it's own- Hopefully this makes sense, otherwise I will try to clarify.

Thanks!


EDIT:
Sorry one tweak that might make it slightly easier-

When opening the Master Template (I.e. the first time the first dialog box opens), it will say something along the lines of "Start Voyage", then allow me to input constants, and will SaveAs as "Current Voyage Report". The Boxes input will allow for a name that's specified from the combination of a box (#), a button (L or B), and two drop down boxes (there contents from from the "Ports" Sheet- the total name will look like "#L Name1-Name2" or #B Name1-Name2" after saving, it will ask for "Noon" or "Arrival". This name will be used when the Arrival (Totals) Sheet is SaveAs at the end.

After the first "Noon" is created, anytime the sheet is opened it will go through with the regular "Noon", "Arrival", or "Modify" dialog boxes options.

Once "Arrival" is filled out, there will be a save button that actually saves the file to a new location and deletes the previously saved copy as stated above.
 
Last edited by a moderator:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

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