Need assistance with VBA (copy/paste data from 1tab into another tab)


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:

Existing Process:
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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobOwner (Aidellio)Commented:
Essentially continue on the same path you have been with your copying and pasting.  To get the next column in the Tracking tab, use this function:

Public Function getNextCell()
    Dim i
    i = 2 'start at col B
    While (Sheet6.Cells(1, i).Value <> "")
        i = i + 1
    getNextCell = i
End Function

Open in new window

ExpExchHelpAnalystAuthor Commented:

Thank you for the response.  I am not entirely clear how to existing code (with column reference) into the new/proposed code.

Could you please elaborate on the VBA (e.g., include the XLS with code)?

Again, thank you in advance.

RobOwner (Aidellio)Commented:
Here is the attached file.  I return the address from the custom function "getNextHalf" that you can find in the module1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ExpExchHelpAnalystAuthor Commented:
Rob - this is absolutely perfect!    

The only thing that's missing is the copying process of the paycheck checks.   That is, both I6 and I18 (merged cells) needs to be copied in row 1 and row 10, respectively, of the next available column (unmerged).    If the "merged" into "unmerged" poses a problem, I will find a work-around.

Any ideas as to how the paycheck dates can be copied over as well?

ExpExchHelpAnalystAuthor Commented:
Rob - copying the paycheck data into into the cells was actually easy.   However, it copied the existing formula (i.e., "='Payday Every Other Week'!$M$4) vs. the output value of "Paycheck (26 Jan)".   Any suggestions as to how to perform a "Paste Special | Text" vs. copying the formulae for both I6:I7 and I18:I19?

Thank you.

ExpExchHelpAnalystAuthor Commented:
Perfect solution!!!!   Thank you, Rob.

RobOwner (Aidellio)Commented:
Sorry, I was sleeping :)

Thanks for the points!  I've updated the spreadsheet to also copy the header
ExpExchHelpAnalystAuthor Commented:
Rob -- most excellent solution.   Again, I thank you for your assistance!!!   :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.