Auto row copy to another worksheet [Closed]

Report
Posts
4
Registration date
Tuesday May 13, 2014
Status
Member
Last seen
May 15, 2014
-
 Anon -
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?

6 replies

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?
Posts
4
Registration date
Tuesday May 13, 2014
Status
Member
Last seen
May 15, 2014

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
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.
Posts
4
Registration date
Tuesday May 13, 2014
Status
Member
Last seen
May 15, 2014

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.
I only suggested the removal of the entries if there was to be a dedicated entry sheet for the batch.
Since you seem to have a master sheet where everything is entered along with all the history of previous batches the flag option is looking more and more like the path to take.
In order to proceed further a copy or sample of your current solution is required. I'll have a play with my own data to prove feasibility.
Excel 2013 does have the Quick Access menu (its the one at the very top left of the Excel window) so the commit macro could be added to there but having thought about it , the button would also be present for any other spreadsheet too so that's no good. A button at the top of the sheet or a simple keyboard shortcut to run the macro could be used instead.
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 Long
Dim lastrow As Long
Dim LastDataRow As Long

LastDataRow = Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row ' find last nonblank row in column A
myrow = 2
While 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 + 1
Wend

End 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.
Posts
4
Registration date
Tuesday May 13, 2014
Status
Member
Last seen
May 15, 2014

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!
That must be a really long trip!!! :)