Consulting

Results 1 to 17 of 17

Thread: I need VBA code to update log worksheet and maintain a history of all orders.

  1. #1
    VBAX Regular
    Joined
    Dec 2012
    Posts
    8
    Location

    Red face I need VBA code to update log worksheet and maintain a history of all orders.

    I have a table that includes list of clients, products they order, and requested delivery date. When the client calls in to place an order, I pull up the excel table (orders worksheet) and fill in the items quantities and the required delivery date for the client who called. Once I fill the information for the client who called in, I need to update a "log" worksheet with the information that I just entered. The log worksheet will eventually contains history of all the orders which I received in the past. I appreciate if somebody can provide VBA code to do the job. I attached the excel file.. thanks a lot!
    Attached Files Attached Files

  2. #2
    VBAX Tutor
    Joined
    Jan 2008
    Posts
    258
    Location
    How about something like this, assign it to your button:

    If there is colour in the first data row, row 9, then data in a column is transferred across.

    [VBA]Sub PopulateLog()

    Dim RowNo As Long
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim LogLastRow As Long
    Dim ColNo As Long
    Dim LastCol As Long
    Dim LogLastCol As Long
    Dim rng As Range

    FirstRow = 9
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    ColNo = 1
    LastCol = ActiveSheet.Cells(FirstRow, Columns.Count).End(xlToLeft).Column

    For ColNo = 1 To LastCol
    If ActiveSheet.Cells(FirstRow, ColNo).Interior.ColorIndex <> xlNone Then
    If Not rng Is Nothing Then
    Set rng = Application.Union(rng, ActiveSheet.Cells(FirstRow, ColNo).Resize(LastRow - FirstRow + 1, 1))
    Else
    Set rng = ActiveSheet.Cells(FirstRow, ColNo).Resize(LastRow - FirstRow + 1, 1)
    End If
    End If
    Next ColNo

    With Sheets("Log")
    LogLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    LogLastCol = .Cells(FirstRow, Columns.Count).End(xlToLeft).Column

    rng.Copy (.Cells(LogLastRow + 1, 1))
    .Select
    .Cells(LogLastRow + 1, 1).Resize(rng.Rows.Count, LogLastCol).Interior.Color = RGB(235, 241, 222)
    End With

    End Sub[/VBA]
    sassora

  3. #3
    VBAX Regular
    Joined
    Dec 2012
    Posts
    8
    Location
    Thanks a lot Sassora!

    The code is brilliant and works great! Thanks a lot! .. However, there are two main issues that, if resolved, would make this code an exact match to what I need as follows:

    1- I really used the colors in cells for illustration purposes to show what fields I need to copy. My actual data does not have colors .. Is it possible to select the fields directly (for example columns "a", "b", "I", etc ) rather than looping through cells and select the ones that are colored?

    2- When I click the button few times, it copies "all" the data into the log again and again ..I really meant to copy only the record that have changed or added, since the others are presumably already exist in the log. To elaborate, when I receive a call from one of the clients, I over-write the old information such as setting new delivery date, quantities, etc for the next shipment. If the record have changed, and is determined that it is not in the log (based on certain fields combination,etc.), then only this record has to be copieg into the log..
    Thanks again!!

  4. #4
    VBAX Tutor
    Joined
    Jan 2008
    Posts
    258
    Location
    Hi, you could always make the colour white (as opposed to no fill) then the code works as is and will be more flexible in the future.

    I suggest you may need an order number in there too, to determine whether an entry can be overwritten? I assume your customers return and you wouldn't want to overwrite their previous orders.
    sassora

  5. #5
    VBAX Tutor
    Joined
    Jan 2008
    Posts
    258
    Location
    Try this, it assumes that column B contains the order number (which of course it is not)

    [VBA]Sub PopulateLog()

    Dim RowNo As Long, ColNo As Long
    Dim FirstRow As Long, LastRow As Long
    Dim LogLastRow As Long
    Dim LogAmendRow As Long
    Dim arr As Variant

    FirstRow = 9
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

    arr = Array(1, 2, 9, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23)

    With Sheets("Log")
    For RowNo = FirstRow To LastRow
    If OrderExists(ActiveSheet.Cells(RowNo, 2), .Columns(2)) Then
    LogAmendRow = Application.WorksheetFunction.Match(ActiveSheet.Cells(RowNo, 2), .Columns(2), 0)
    For ColNo = 1 To UBound(arr) + 1
    .Cells(LogAmendRow, ColNo) = ActiveSheet.Cells(RowNo, arr(ColNo - 1))
    .Cells(LogAmendRow, ColNo).Borders.LineStyle = xlContinuous
    .Cells(LogAmendRow, ColNo).Interior.Color = RGB(235, 241, 222)
    .Cells(LogAmendRow, 3).NumberFormat = "m/d/yyyy"
    Next ColNo

    Else
    LogLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    For ColNo = 1 To UBound(arr) + 1
    .Cells(LogLastRow + 1, ColNo) = ActiveSheet.Cells(RowNo, arr(ColNo - 1))
    .Cells(LogLastRow + 1, ColNo).Borders.LineStyle = xlContinuous
    .Cells(LogLastRow + 1, ColNo).Interior.Color = RGB(235, 241, 222)
    .Cells(LogLastRow + 1, 3).NumberFormat = "m/d/yyyy"
    Next ColNo

    End If
    Next RowNo
    End With

    End Sub

    Function OrderExists(Order As Range, Col As Range) As Boolean
    On Error Resume Next
    OrderExists = Not IsError(Application.WorksheetFunction.Match(Order, Col, 0))
    End Function[/VBA]
    Last edited by sassora; 04-21-2013 at 07:20 AM.
    sassora

  6. #6
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,111
    Location
    [VBA]Sub UpdateLog()

    Dim OrderRow As Long
    Dim NextRow As Long
    Dim ColumnsToCopy As Variant
    Dim LogData As Variant
    Dim i As Long

    'Thanks to Sassora for ColumnsToCopy
    'The order in which Col Nums are placed in ColumnsToCopy matches the
    'order in which they are pasted in Sheet "Log"
    ColumnsToCopy = Array(1, 2, 9, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23)
    ReDim LogData(UBound(ColumnsToCopy))

    OrderRow = Selection.Row
    With Sheets("Orders")
    For i = 0 To UBound(ColumnsToCopy)
    LogData(i) = Cells(OrderRow, ColumnsToCopy(i)).Value
    Next i
    End With

    NextRow = Sheets("Log").Cells(Rows.Count, 1).End(xlUp).Row + 1
    With Sheets("Log")
    For i = 0 To UBound(LogData)
    .Cells(NextRow, i + 1) = LogData(i)
    Next i
    End With
    End Sub
    [/VBA]
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  7. #7
    VBAX Regular
    Joined
    Dec 2012
    Posts
    8
    Location
    Thanks Sassora! .. and thanks SamT! ..

    Yes I agree, the customers are ongoing and they typically place / confirm the next order every 2 or 3 weeks. And yes, I do not want to overwrite their previous "orders". However, I wanted to achieve this through the "log" sheet .. The "log" sheet is supposedly be the place where I intend to record and keep history of all the previous orders .. The "orders" sheet is not intended to store history of all previous "orders" .. I wanted rather to make it just a recording form for any new order .. I consider the "orders" sheet as a database, with only one row for each customer, and I also utilize it to record/ confirm the next delivery. If I overwrite the next order date and quantity, a new row would be created in the "log" sheet, and the new order information will be appended to it (the "log" sheet is the place to keep history of all previous "orders", not the "orders" sheet) .. I think a combination between the account number (column 2) and the date ( column 9), of the "orders" worksheet would represent the unique key to determine if the order is really a new order and therefore will be decided on if it will be added to the log or not .. The solution which is provided so far is excellent, however, it seems that you intended to have the functions of both worksheets reversed to what I am trying to describe .. I appreciate if you advice on if what I need is doable and could the solution be modified to achieve what I described? Thanks a lot

  8. #8
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,111
    Location
    My code does exactly what you want, Just assign it to the button on sheet "Orders."

    I would add a "Comments" Column to the right of the others. Just Add the column number 24 to the end of the ColumnsToCopy array.

    In fact, no matter what changes you make to the two sheets, the only adjustments to the code take place in the ColumnsToCopy array.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  9. #9
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,111
    Location
    Second post because the forum server is very busy ATT, and it's too easy to lose a post.

    You can't use the date as a Key, since it is possible for a customer to place two orders on the same date, or even if you want to record changes in an order.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  10. #10
    VBAX Tutor
    Joined
    Jan 2008
    Posts
    258
    Location
    Quote Originally Posted by SamT
    You can't use the date as a Key, since it is possible for a customer to place two orders on the same date, or even if you want to record changes in an order.
    This is why I suggested using an order number; to make this distinction. Choose a column for it and I'll adapt the match function to include it.

    Note that: the last bit of code I posted, was only based on an array of columns and not dependent on changes to the sheet.
    Last edited by sassora; 04-22-2013 at 04:31 AM.
    sassora

  11. #11
    VBAX Regular
    Joined
    Dec 2012
    Posts
    8
    Location

    Wink

    Thanks again Sassora and SamT! ..

    I apologize, I may be missing something .. I applied all the provided solutions in the attached file (3 buttons)..

    For some reason, it still does not provide my desired output in the "log" worksheet at 100% !! I may be missing something or did not explain what I really wanted very well ..

    In the attached file .. please check the third worksheet called "desired sequence" . I "visually" listed what I needed in 3 sequential steps shown for both the "orders" and "log" worksheets .. This may clarify the final desired output in the "log" .. Please advise if any of the buttons that I added should provide the final outcome, or am I missing something .. Again I really appreciate your efforts and your contribution to this great forum .. thanks again!!
    Attached Files Attached Files

  12. #12
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,111
    Location
    Sassora,

    Yes, the array of columns was brilliant, I will remember that schema.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  13. #13
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,111
    Location
    Adfoka,

    The macro seems to work as you have said you wanted it to. I might be misunderstanding what you want.

    Note:
    No macro will run while a Cell is in Edit Mode. That is, when a Cell is ready to accept keyboard input. This simply means that when you are ready to record an order onto the Log sheet, you just have to insure that you have left the last Cell edited. This can be into the Cell after the last column in the Order Table.

    I noticed a problem with my code. It ran even if you selected Cells in more than one Row. That is fixed.

    I took the liberty of adding a Comments Column at the end of the Tables. It does not need an entry for the code to work.

    I used the Excel (2002) Menu; Window >> Freeze Pane to keep the first few columns in the tables fixed so they were always visible, thus keeping the Button in sight even when you are at the end of the table.

    For your convenience, I added a line of code at the end to move the cursor back to the first Row when the button is clicked.
    [VBA]Sub UpdateLog()

    Dim OrderRow As Long
    Dim NextRow As Long
    Dim ColumnsToCopy As Variant
    Dim LogData As Variant
    Dim i As Long

    With Sheets("Orders")
    If Selection.Rows.Count > 1 Then
    MsgBox "Select Cells in only one Row!"
    Exit Sub
    End If

    'Thanks to Sassora for ColumnsToCopy
    'The order in which Col Nums are placed in ColumnsToCopy matches the
    'order in which they are pasted in Sheet "Log"
    ColumnsToCopy = Array(1, 2, 9, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24)
    ReDim LogData(UBound(ColumnsToCopy))

    OrderRow = Selection.Row
    For i = 0 To UBound(ColumnsToCopy)
    LogData(i) = Cells(OrderRow, ColumnsToCopy(i)).Value
    Next i
    End With

    NextRow = Sheets("Log").Cells(Rows.Count, 1).End(xlUp).Row + 1
    With Sheets("Log")
    For i = 0 To UBound(LogData)
    .Cells(NextRow, i + 1) = LogData(i)
    Next i
    End With

    Sheets("Orders").Cells(Selection.Row, 1).Activate
    End Sub[/VBA]

    In the attachment, it was easiest for me to change the Account number to indicate which order I was recording. I started at Number 1 and changed the Account number in each Row before I recorded that order.
    Attached Files Attached Files
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  14. #14
    VBAX Regular
    Joined
    Dec 2012
    Posts
    8
    Location
    Thanks a lot SamT !! I really appreciate it .. It did not seem to work before, but now I know how it works!! ..

    After I do all the changes, I have to click out of the last edited cell for the macro to work ... and, if I click outside the edited record row (i.e. different row, as I probably have done!) it did not seem to work either, since the "selected row" goes beyond the scope of the changes , which seem to be a requirment fot the macro..

    I very much appreciate your and sassora's assistance on this.. I finally got what I was looking for!! thanks again !!

  15. #15
    VBAX Newbie
    Joined
    Feb 2020
    Location
    Port Harcourt
    Posts
    1
    Location
    I need help urgently.
    I have an excel project am working on, it's an assignment to develop a monthly schedule staff system using excel. But am stuck at a point, I want to add a log sheet to it, to keep all monthly entries(history), and I want another sheet to keep record of when a staff will be on duty and off duty (the scheduling pattern is two (2) months in and one (1) month out). Please can somebody help. Thanks in advance.

  16. #16
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,008
    Location
    Quote Originally Posted by Peter John View Post
    I need help urgently.
    I have an excel project am working on, it's an assignment to develop a monthly schedule staff system using excel. But am stuck at a point, I want to add a log sheet to it, to keep all monthly entries(history), and I want another sheet to keep record of when a staff will be on duty and off duty (the scheduling pattern is two (2) months in and one (1) month out). Please can somebody help. Thanks in advance.
    1. Welcome to the forum - please take a minute to read the FAQs in my signature

    2. This is a 7 year old thread. Better to start a new one using the (+ Add New Post) button at the top of the first page

    3. It's not polite to hi-jack a post with one that doesn't have any relationship to it

    4. Unfortunately this isn't a free coding service. We'll be happy to answer questions or look at issues for you

    5. If you have a workbook with what you've done, and a specific question(s), just attach to (preferably to your own new thread)
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  17. #17
    Banned VBAX Regular
    Joined
    Feb 2020
    Posts
    8
    Location
    I really used the colors in cells for illustration purposes to show what fields I need to copy. My actual data does not have colors .. Is it possible to select the fields directly (for example columns "a", "b", "I", etc ) rather than looping through cells and select the ones that are colored?

Posting Permissions

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