Creating Monthly Workbooks from Old, Saving as New Month, Keeping Original Format and Formulas, And bringing in one column of data

dsharp

New Member
Joined
Jun 18, 2018
Messages
2
My apologies up front for being a complete neophyte when it comes to VBA, and not much better in regards to Excel. However, in a world where no-one knows ANYTHING about Excel, the man who can get cells to add up is 'king'. So, here at work, I'm it. I apologize, too, for the length of this post, but I want to be as thorough as possible in explaining what I want/need, since it seems complicated (to me), but I'm hoping I can figure out a way to make it work.

I've been tasked with trying to get a dismal inventory (non)system computerized. I have created a workbook that has 2 sheets of inventory that gets counted. Those numbers then populate 4 other sheets that make up 2 separate reports that have to be emailed to different people at the end of each month. That's been done.

That workbook then needs to be saved for future reference, and a new workbook created for the next month to do it, again.

Now, keep in mind that this system has to be able to be used by people who, literally, can only (maybe) enter numbers in the appropriate cells, and then save a document. We're talking BARELY able to function on the computer. Therefore, I have to keep this as simple as possible for the end-user.

With that in mind, I may need to break the single monthly workbook into 3 different books - one for the count and entering the numbers (2 pages), one for Report 1 (3 pages), and one for Report 2 (1 page). This would make it easier for them to select and forward the reports to the people they need to send them to, without sending the entire workbook to everyone. That would be best, but if it's too complicated we can just make it work somehow with the single workbook.

If these pages aren't in the same book, then they'd have to be in a file with that month's date on it to make it easy to find later for auditing purposes. I think that would make the process of 'automating' the ongoing months even harder, though, but I don't know.

Anyway, once we've finished entering the numbers for May, we need to have a simple way to create the new workbook(s) for June, but with the same formatting as May's workbook(s), and with the same links/formulas - except pointing to the June numbers now, and of course to save them as the June workbook(s). I'm thinking that the 'easiest' way for them to do this is to have a 'button' they could use on the workbook(s) that would run a script to accomplish all of this - but I don't know what the "$&^" I'm doing, so that may not be the best/easiest way. Shoot, I don't even know if all of this is possible, but it seems like it should be! It's just figuring out how to tell the computer what I want/need, right?

Of course, once we've finished the workbooks for 2018, we'd need to be able to continue the process for the next year, as well.

Anyway, I'm hoping someone will see this and help me out.

thanks, in advance!
doug
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
I forgot to mention in the explanation - I would need to carry one column of data (End of Month #'s) over to one of the new pages to populate the beginning #'s, too. I suppose there could be some other numbers that I could carry over, as well, if I thought about it, but that would be the minimum.

Thanks, again!
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

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