• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 72
  • Last Modified:

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

Experts:

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.

EEH
Monthly-Bills.xlsm
0
ExpExchHelp
Asked:
ExpExchHelp
  • 5
  • 3
1 Solution
 
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
    Wend
    getNextCell = i
End Function

Open in new window

0
 
ExpExchHelpAuthor Commented:
Rob:

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.

EEH
0
 
RobOwner (Aidellio)Commented:
Here is the attached file.  I return the address from the custom function "getNextHalf" that you can find in the module1
Monthly-Bills-Rob.xlsm
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
ExpExchHelpAuthor 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?

EEH
0
 
ExpExchHelpAuthor 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.

EEH
0
 
ExpExchHelpAuthor Commented:
Perfect solution!!!!   Thank you, Rob.

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

Thanks for the points!  I've updated the spreadsheet to also copy the header
Monthly-Bills-Rob.xlsm
0
 
ExpExchHelpAuthor Commented:
Rob -- most excellent solution.   Again, I thank you for your assistance!!!   :)
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now