auto row copy to another worksheet Closed

Question

I'm new to excel and need to set up a simple double entry bookkeeping system in excel.  I need to know what formula to put into a cell to accomplish the following.  
On our accounting page, there is a column called "Category".  In that column, we will put a letter/number corresponding to a particular category.  I would like excel to automatically copy that row of data to a separate sheet that contains only the expenses for that particular category.
For example, if on the main sheet, we spend $100 for gasoline and in the category column we put that category code "T" (for transportation), then we would like for Excel to automatically copy that row to another worksheet within the same workbook specifically for that category (Transportation Expenses).  How do I do this?

RayH · Answer

A couple of questions for clarification and food for thought:
1. At what point during your process should the row be committed to the other worksheet? Immediately upon pressing Enter?
2. What if a category is entered incorrectly and then changed to a different one? How would the process know to replace it in the other sheet?
3. What if an extraneous entry is made? How would the process know to remove it from the other sheet?
4. What if the process is handled in batches. That is, entries are made and verified before the new rows are committed to the other sheets?

barryhall · Answer

A couple of questions for clarification and food for thought: 
1. At what point during your process should the row be committed to the other worksheet? Immediately upon pressing Enter? 
2. What if a category is entered incorrectly and then changed to a different one? How would the process know to replace it in the other sheet? 
3. What if an extraneous entry is made? How would the process know to remove it from the other sheet? 
4. What if the process is handled in batches. That is, entries are made and verified before the new rows are committed to the other sheets?

I hadn't thought about those problems.  I think the answer to all of your questions can be best answered by your final suggestion, a batch file that can be run after all the corrections have been made and confirmed.  That's a very good suggestion.  Thank you so much for your help.

With this additional information, can you still help me with how to do the batch?

Sincerely,

Barry

RayH · Answer

Without seeing how your workbook is set up its difficult to tell how it would fit in.
But I imagine a data entry sheet for the batch of entries with a button to run the macro along the top (above the 'split row' so it doesn't disappear) when scrolling. The macro could remove these entries from this sheet after the commit has taken place.
If this is not how it looks then again I would place the button at the top (or even in the quick access menu depending on the version you are using).
The macro could look for entries made that day or flagged as being new (a simple X in a column to indicate this that this cleared after the commit has taken place).
Perhaps a flag or indicator to be used to indicator that the entry should be removed or changed so the macro runs in update mode rather than add/insert mode.
I guess the real answer is, it depends.

barryhall · Answer

Sounds good!

In response to your questions, I don't want the macro to remove the entries from the original main page, just copy them to the secondary pages.
It would be a good idea that the macro/batch flags the entries on the main page showing that they have been copied.  Therefore the subsequent times that we hit the macro execution button, it would only copy those not having been flagged earlier.

I'm running Excel 2013.

RayH · Answer

I did a quick and dirty mock up of the solution. It could be made to be more efficient in my opinion but it gets the point across.
The workbook I made consists of 4 tabs: Data, A,B C
The header in the Data sheet is:
flag	Date	model	price	trancode

The header in the other sheets is:
Date	model	price	trancode

The code to commit the new entries is:
Sub commitdata()Dim myrow As LongDim lastrow As LongDim LastDataRow As LongLastDataRow = Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row ' find last nonblank row in column Amyrow = 2While myrow <= LastDataRow   If Worksheets("Data").Cells(myrow, 1) = "X" Then ' ok found an entry to process    Select Case Worksheets("Data").Cells(myrow, 5)        Case "A" ' copy row to Sheet A            LastRowNum = Worksheets("A").Range("A" & Rows.Count).End(xlUp).Row + 1            Worksheets("Data").Range("B" & myrow & ":E" & myrow).Copy Destination:=Worksheets("A").Range("A" & LastRowNum)            Worksheets("Data").Cells(myrow, 1) = ""                    Case "B" ' copy row to Sheet B            LastRowNum = Worksheets("B").Range("A" & Rows.Count).End(xlUp).Row + 1            Worksheets("Data").Range("B" & myrow & ":E" & myrow).Copy Destination:=Worksheets("B").Range("A" & LastRowNum)            Worksheets("Data").Cells(myrow, 1) = ""                    Case "C" ' copy row to Sheet C            LastRowNum = Worksheets("C").Range("A" & Rows.Count).End(xlUp).Row + 1            Worksheets("Data").Range("B" & myrow & ":E" & myrow).Copy Destination:=Worksheets("C").Range("A" & LastRowNum)            Worksheets("Data").Cells(myrow, 1) = ""    End Select   End If    myrow = myrow + 1WendEnd Sub

What do you think? Again, its not very tidy.
Code could be added to check for flag types other than X, to delete or update.
If a trancode other than A,B or C is found then it is left as is.

barryhall · Answer

Thank you so much!  I'm leaving on a trip and even though I'd like to, I can't get to checking all this out at the moment.  But I definitely will when I get back.  I greatly appreciate your help.  We are an orphanage in the center of the Amazon region of Brazil and we survive on everyone doing their part to help out.  You've just done yours and we greatly appreciate it!  I'll get back with you again after my trip.  Again.  THANKS SO MUCH!

Auto row copy to another worksheet

6 responses

Similar discussions

Subscribe To Our Newsletter!