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

[Adfoka]

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!

[sassora]

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.

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

[Adfoka]

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!!

[sassora]

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]

Try this, it assumes that column B contains the order number (which of course it is not)

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

[SamT]

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

[Adfoka]

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

[SamT]

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.

[SamT]

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.

[sassora]

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.

[Adfoka]

Thanks again Sassora and SamT! ..

I apologize, I may be missing something :dunno .. 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!!

[SamT]

Sassora,

Yes, the array of columns was brilliant, I will remember that schema.

[SamT]

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.
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

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.

[Adfoka]

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 !!

[Peter John]

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.

[Paul_Hossler]

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)

[poielsd]

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?