I have an existing spreadsheet that allows me track monthly bills. Below describes the "existing process" as well as the "envisioned (modified) process". As a picture is worth a 1000 words, please view the attached XLS which should further illustrate as to what I am trying to achieve:
1. $$ amounts in columns I, L, and O represent bills from "last month", "present month" and "next month", respectively.
2. When transitioning into a new month, I run the command button "Run Update (1st of Month").
3. Running the command button will "move" (VBA copy/paste") data from "present month" (columns L:M) into columns "last month" (columns I:J). At the same time, it will aso "move" (VBA copy/paste") data from "next month" (columns O:P) into "present month" (columns L:M).
4. Lastly, "next month" (columns O:P) are populated with default values hard-coded in the VBA.
5. Essentially, all amounts are shifted to the left when pressing the command button.
Modified Process Should Accomplish the Following:
1. Per existing process, I "lose" values from "last month" (i.e., they are overwritten with values from "present month") once the command button is executed.
2. Instead of losing those historic values though, I now would like to track them on another tab ("Tracking of Bills").
3. That is, last month's values (I:J) and the date [e.g., 'Paycheck (26 Jan)' and 'Paycheck (09 Feb)'] should be copied to the new tab (column B).
4. Here's the challenging element though... next month, I do NOT want to overwrite the values into column B (on new tab). Instead, the next empty column is column C. Thus, as time goes on, I still maintain bills from previous months. So, the new tab "Tracking of Bills" will allow me to maintain a history of bills. How can this be accomplished?
Any assistance with developing the required VBA would be much appreciated. Thank you in advance.