|re: The idea is that when I make an entry in the main check register and click the Update Balances command button all the entries for that specific category are copied to their existing worksheet with the same name. |
That request is fine for the initial set-up of each category sheet, but I don't think you want to copy all the entries every time you make a change.
I assume you only want to add the latest entries.
The code below will copy all of your existing entries to each sheet, but you need to let us know how you to handle subsequent updates.
Do you want to click the button after each change (easy to code) or do you want to click the bottom after a series of changes (doable, but a little more difficult since the code will need a way to determine how many new entries you just made.)
The other option is to have the code run automatically after you make a change to a specific column, such as the "Credit" column, but you would need to make sure that that column is the last one you change so that it copies all of the data (B:G) for that entry.
Let us know what you have in mind.
Note: The name of your category Sheets has to match the text in Column D of your Register sheet exactly for this to work.
Make sure you try this code in a back-up copy of your workbook in case something goes terribly wrong. Macros can not be undone!
Dim catSht As String
Dim myTrans, lastTrans, transRow As Integer
'Find last Transaction in Register sheet, Column D
lastTrans = Sheets("Register").Range("D" & Rows.Count).End(xlUp).Row
'Loop through Column D, copying B:G to the appropriate category sheet
For myTrans = 6 To lastTrans
'Determine Category Sheet name
catSht = Sheets("Register").Range("D" & myTrans).Value
'Find last row in Category Sheet
transRow = Sheets(catSht).Range("D" & Rows.Count).End(xlUp).Row + 1
'Skip Row 5
If transRow = 5 Then transRow = 6
'Copy line to Category Sheet
Sheets("Register").Range("B" & myTrans & ":G" & myTrans).Copy _
Destination:=Sheets(catSht).Range("B" & transRow)