+ Reply to Thread
Results 1 to 9 of 9

copy and paste data month to month

  1. #1
    Registered User
    Join Date
    02-15-2013
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    copy and paste data month to month

    I update vehicle mileage on a monthly basis and need a macro to populate a yearly page by month

    mileage sheet has odometer in column K and dates in column O, the date changes everytime I update the mileage

    I need to populate the yearly sheet according to the corresponding month, so if I update in Feb it should place odom reading in February column on yearly page, when I update in March put same in March column of yearly.
    Any help is appreciated
    Thanks

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: copy and paste data month to month

    Do you have a sample file you can upload?

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    02-15-2013
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: copy and paste data month to month

    Its a pretty straight forward two pages
    hope this helps
    it has a reports page with avg mileage macro buttons but they dont work the way they should
    Thanks
    Attached Files Attached Files
    Last edited by hotrod4571; 02-18-2013 at 04:11 PM.

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: copy and paste data month to month

    Can you explain with a lil detail as to what you exactly require the buttons to do? Also there are 4 buttons, so you want the last 2 to be functioning right?

  5. #5
    Registered User
    Join Date
    02-15-2013
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: copy and paste data month to month

    The first two work ok, the last two do not. the last two buttons are supposed to take the mileage from the mark or unmarked page and apply it to the corresponding month in the avg pages. What happens when you hit the buttons now is that it puts the same mileage in every months column. so at the end of twelve months they are all the same when the should be different and then total at the end
    Thanks

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: copy and paste data month to month

    Can you explain how the calculations are done manually? And then i can help in replicating them through code.

  7. #7
    Registered User
    Join Date
    02-15-2013
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: copy and paste data month to month

    OK I thought I did that, but I'll try again

    1) I enter the recent mileage for sheets A010 thru BVHQ, lets say it January.

    2) If any vehicles are out for repair, column "N" under status I would select, MVA or Body Shop.

    3) I then hit the Populate Repair sheet macro and it populates the repair page with only the vehicles that fit that criteria, I've managed to place a macro on the sheet itself that removes duplicates and sorts the rows in ascending order, because everytime you hit the repair button it will comtinue to populate the sheet with the same items continueing to add the same ones over again.

    4) once all the mileages are entered per page I hit the "populate mark / umarked sheets macro" . This populates the marked and unmarked pages accordingly. It also carries over the date that the mileage when updated. That button works.

    5) Here is the problem. The "calculate marked mileage macro" and "populate unmarked mileage macro" should take the mileage from the unmarked or marked pages and populate the corresponding "marked avg" and "unmarked avg" sheets. It should place the mileage in the column of the month that matches the date when the mileage was inputed. This date is in column "O" of the "marked and unmarked" pages.

    Then next months' (Februarys'), mileage should be put in the FEBRUARY column of marked avg and umarked avg sheets. the problem is that everytime you hit macro buttons 3 and 4, it add the current mileage to the next column as well as the prior, by the end of twelve months they all show the same mileage rather than what they were each one of those months that they were inputed.

    I hope that helps, I'm about to give up on it.

    Thanks

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: copy and paste data month to month

    A few questions for you -

    The Marked and Unmarked sheets would have data for the whole year right? So once you run the macro (that i will help you create) to populate the averages, is there a chance that the January data will be changed? Also, when you run the macro, should data be populated from January onwards in the average sheet or only for the current month?

    I went through your existing codes which you explained upto point 4. I can help you re-write them to reduce the repetition in code.

  9. #9
    Registered User
    Join Date
    02-15-2013
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: copy and paste data month to month

    Q) The Marked and Unmarked sheets would have data for the whole year right?
    ...It's seems like thats what it's doing but seems rather overwhelming to have to continually add to that page month after month. At the end of the year the Marked and unmarked page would be huge.don't know if we can change it sone how? but if it has to be that way so be it.

    Q) So once you run the macro (that i will help you create) to populate the averages, is there a chance that the January data will be changed?
    I wanted to populate the avg sheets with the mileage I had inputed at the beginning of every month. so If the marked and unmarked pages continually compile the mileages for january and feb and march and so on.. then the dates sould be there for the macro to polulate the correct months on the avg sheets.

    Q) Also, when you run the macro, should data be populated from January onwards in the average sheet or only for the current month?
    January and onwards, each month should be going up, and at the totals column have the yearly total which would probably be dec minus jan, and a final column producing a monthly avg of 12 months. Actually it would only be 11 for now since I dont have Dec 2011 numbers for a starting reference. But going toward next year I can use this Dec 2013 as a start for 2014 . that way I would have a 12 months next year.
    dec 2013 jan 14 feb 14 and so on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1