Need help with VBA codes

Copper Contributor

Hello, I need help with a workbook. Each month I want to click a button to start a new month - by starting a new month I want all figures listed in the column named, "current disbursement" to be moved into the "previous fundings" column. I will enter my new current numbers manually.

 

With the next month, how do I add what was already listed in previous fundings to what needs to be moved over from current? 

 

As soon as I zero out the current column, my previous fundings column takes its cue and zero's itself out as well. 

If I manually enter the number into the previous fundings column, excel won't know that next time I want to add it to something else. 

 

Thank you in advance for any suggestions!

4 Replies

@Devon500 

 

Oh, this raises so many questions in my mind.

  • "Where are all the numbers coming from?" for example. 
  • "Is there backup detail for (potentially) multiple transactions underneath, say, "Current Disbursement"?
  • "What happens to any given month's figures after they've been moved off this page?" (said another way, Is any history kept? If so, where? If not, why not?)

 

I could go on.

 

I'm a person with a database background. I see a spreadsheet such as you have here as a summary of a bunch (pretty surely a BIG BUNCH) of other transactions. Transactions the details of which you'd also want to be able--I would think--to report on or supply as backup to this kind of monthly/quarterly summary. Obviously (I think) there is a set of categories off to the left that have intentionally been removed from the test sheet you've posted; that's fine.....need to preserve confidentiality, and I respect that...

 

A database could be used as the background to this as a "dashboard" spreadsheet that retrieves the data on all those transactions and presents a snapshot for any given month "on demand."

 

Is that in fact what's happening? It doesn't sound like it, given both your questions and your description of adding numbers manually into various rows. 

 

So I'm asking just to probe whether or not there's any interest in a different approach altogether. If not, that's fine.....I have to defer to others anyway when VBA is involved; as noted, my perspective is that of a database person.

There is definitely whole bunches of other information associated with each line of the spreadsheet. This is intended to sum it up with just the top layer of information. I am open to other approaches. The sheet I'm describing was used at a previous location of employment and it was a thing of beauty. Also password protected so I am trying to recreate it. The goal is to have a saved file for each month showing exactly what we asked for reimbursements on that month and which funding source was allowed to pay for which line item. With recreating this document, I am hoping to avoid user error in that we now need to manually add the figure in "current" to "previous" so we can start a fresh sheet of the new current requests. It's just asking for problems. I know that Excel can do this for me but I cannot figure out the language to use. I have a feeling that I will need to create multiple macros and break things down to one little step at a time but I'm stuck on the first step! :)

@Devon500 

 

Well, I'm glad you're open. I'm--long retired--not in the business of consulting on and helping clients create advanced accounting databases and monthly summary reports. I can pretty confidently assure you that you'd be far better served by building a solid database in the background, a database in which you record the individual transactions that constitute the "bunches" of data that lie behind these summary numbers.

 

As a trivial example of the kind of thing I'm talking about, I have a database that consists of every check written, every income item received, every credit card payment (downloaded from the various banks in every case)....I add budget categories to each line of the database...and then use Excel (Pivot Table, generally) to summarize monthly totals for each budget category. I can (through the magic of the Pivot Table) get instant backup details on any monthly figure in any budget category. How many macros are involved in this workbook? Zero. It's all done with standard, built-in Excel functions. Not to say that macros or VBA routines aren't useful; they are when needed. But they're often not needed, and (potentially) less reliable, if only because less tested, than standard built-in routines.

 

So I don't know what your budget is to develop this in a different way, and I'd be happy to give a more detailed description of what I'm hinting at, but I think you and your organization would be well served by taking a step back from the monthly summary itself to design a database as the foundation of a more robust monthly reporting system.

@mathetes We do have a GL system which serves as the larger database for global info - capabilities of sorting on the totals that have been spent on one code or another. This sheet really needs to represent only the current request and the summary totals of previous requests. The secondary piece would purely be there so everyone can see we didn't spend more than the agreed upon amount per line item - or to prove that we do have a few dollars left for that line item. Thank you for your input! I will definitely continue brainstorming on how to make this even better