Copy & Update Rows To Another Worksheet

    Hopefully someone can point me in the right direction, since I'm sure this problem has been asked to death already, but I just can't find the answers...


    I have 2 worksheets in a workbook: "Orders" and "History". Both sheets contain the exact same columns. The "Orders" page data gets wiped clean at the start of each month. What I'm looking for is a macro solution that will copy any new rows and update any old ones that have changed on the "Orders" page to the "History" page, so we can keep a running history of sales. Ideally, I'd like the macro to fire only when performing a Workbook Save or Workbook Save As.


    "History" is in date order, so adding new entries from "Orders" should be easy, just by adding rows to the bottom. Updating existing rows will be harder, because I want it to update if the row has changed but not be deleted or changed when the "Orders" sheet is wiped clean the 1st of the month. Any ideas?


    Additional:


    The columns in both sheets are:


    Order Date
    Ship Date
    Order Number
    Customer
    Item
    Qty
    Amount


    Because each item gets a seperate line, there will be rows that have identical Order Date, Ship Date, Order Number, and Customer entries, so I suspect I will need to compare multiple columns when deciding if one has changed (the only column that *should* change after initial entry on the orders page is the Ship Date, which would initially be left blank when the order is first entered).

    I'm just here until I win the lottery.

    Re: Copy & Update Rows From One Sheet To Another When Saving


    Hello, QBPaladin!


    Could you possibly attach a copy or example of your file? It would be much easier to work it out.


    Mac

    Re: Copy & Update Rows From One Sheet To Another When Saving


    Here's a sample with junk entries, but it shows what the columns are and what the sample data looks like. In this sample, the data for February 1 exists on both the Orders sheet and the Historic sheet (had to change the sheet name because History by itself was a reserved word), but the data for February 2 is only on the Orders sheet.


    I need a macro that will copy all the new rows (February 2, in this case) to the Historic page, as well as updating any existing entries on the Historic page that have changed on the orders page (by having the Ship Date filled in, for example). Of course, the macro has to be able to get the new entries each day, not just February 2.

    Re: Copy & Update Rows From One Sheet To Another When Saving


    Quote from Macropheliac

    Take a look at the attachment. Is that what you need? If this is it, then I'll explain how to run the code when saving.


    Mac


    Mac, you're a genius!


    Everything seems to run great except one thing. When the data is wiped on the Orders page and the macro runs with a blank sheet (which shouldn't ever happen...but you know it will once end users start working with it), it copies the header row over to the Historic page. Other than that, if you can explain how to make it run when saving, you've solved my problem!

    I'm just here until I win the lottery.

    Re: Copy & Update Rows From One Sheet To Another When Saving


    Hello again, QBPaladin!


    Thanks for the compliment. I think this may be what you want. I added the following line at the beginning of the "UpdateOrders" code so that it will not run when the sheet is "wiped".


    Code
    If Sheets("Orders").Range("A4").Value = "" Then Exit Sub


    I added the following code to the BeforeSave event of the ThisWorkbook module to run the code each time the workbook is saved:


    Code
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Run "UpdateOrders"
End Sub


    I hope this helps.



    Mac

    Re: Copy & Update Rows From One Sheet To Another When Saving


    Hi Mac. After moving the macro into my real worksheet, I'm running into a problem...


    The real sheet has additional columns in it. I think that's making the Offset functions not work correctly. New lines on the order page are moving over fine, but updated lines are being treated as new, and I can't tell which columns are being checked looking at your macro. Can you walk through an explaination of what the loops are doing, especially how the Offsets are set up?


    For-next loops I can kind of figure out from my Applesoft basic days (I'm old), but I've never seen a Do-while loop.


    Also, it's pretty slow on my real worksheet. I have over a year's worth of historic data on it already, and I think the loop is looking though all of it for changed rows. How can I modify the macro to get the last date entered on the Orders page and restrict the loop on the Historic page so it only looks back 30 days?

    I'm just here until I win the lottery.

    Re: Copy & Update Rows From One Sheet To Another When Saving


    Anyone? I've bumped this thread twice, but they disappeared and Mac hasn't replied.

    I'm just here until I win the lottery.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login