Copy entire row to one of three sheets based on data in one column

[Rich_Kiroy]

I am working on a workbook that will have four sheets, each is a bank register. The first is the main register that houses every transaction associated to the entire account. The remaining three are what I am calling "T-Accounts". In Column "I" (labeled "T-Account") there will only be three possible entries; NCHF, Riders, and COP. These data entries are also the names of the remaining three sheets.

When data is entered into the main register, I want Excel to see what entry is placed in the column "I", and then copy the entire row to the corresponding sheet. Where I will set up a sum function to total the balance of the "T-Account". I have no idea where to start so any help will be very appreciated, I must say I have no experience working with macro's or VBA at all, this is very new.

 

[Michael M]

just passing through...but
This will copy data from the "Main" sheet to the required other sheets based on Column I of the main sheet
To copy / paste the code
Press ALT F11
Select the workbook in the LH pane
Paste the code provided in the RH pane
close the VBA explorer

To run the code
Press ALT F8 select the code from the menu "MM1"
press "run"

Sub MM1()
Dim lr As Long, r As Long, lr2 As Long
lr = Sheets("Main").Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 1 Step -1
    Select Case Range("I" & r).Value
    Case Is = "NCHF"
         lr2 = Sheets("NCHF").Cells(Rows.Count, "A").End(xlUp).Row
        Rows(r).Copy Sheets("NCHF").Range("A" & lr2 + 1)
    Case Is = "Riders"
         lr2 = Sheets("Riders").Cells(Rows.Count, "A").End(xlUp).Row
        Rows(r).Copy Sheets("Riders").Range("A" & lr2 + 1)
    Case Is = "COP"
        lr2 = Sheets("COP").Cells(Rows.Count, "A").End(xlUp).Row
        Rows(r).Copy Sheets("COP").Range("A" & lr2 + 1)
    End Select
Next r
End Sub

 

[Rich_Kiroy]

Michael,
Thanks for the tip, I will try it tonight when I return home from work.

 

[Rich_Kiroy]

Hello Michael,
So I couldn't wait until this evening. The script works but I have one issue with it, how do I make it recognize and only copy new data to the various sheets. When I run the script all data from the main sheet is copied so I end up with duplicate entries.

 

[Rich_Kiroy]

I have been working on a work book that basically has four check registers, one main register that I call "Check Register" and three sheets that represent registers for "T-Accounts" called: NCHF, COP, and Riders. I have a macro with three sub commands, the first runs the remaining two in the following order. 1. Removal - This removes all the data from the T-account sheets to prevent duplication of data. 2. MM1 (Code provided by Michael M - Thanks Michael), this code reads the main register and based on the data in column "J" copies all the rows to the appropriate sheet named after the corresponding "T-account". This works well with one exception, the order of the data as it copies back in is from newest (at top) to the oldest entry on the bottom. This breaks my formula(s) for maintain a balance. I am looking for help to prevent this. Here is the entire code as I am running in my Macro: Thanks in advance for any help.

Public Sub Master()
Removal
MM1
End Sub


Public Sub Removal()
ActiveWorkbook.Sheets("NCHF").Range("A2:J102").Clear
ActiveWorkbook.Sheets("Riders").Range("A2:J102").Clear
ActiveWorkbook.Sheets("COP").Range("A2:J102").Clear
End Sub


Sub MM1()
Dim lr As Long, r As Long, lr2 As Long
lr = Sheets("Check Register").Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 1 Step -1
Select Case Range("J" & r).Value
Case Is = "NCHF"
lr2 = Sheets("NCHF").Cells(Rows.Count, "A").End(xlUp).Row
Rows(r).Copy Sheets("NCHF").Range("A" & lr2 + 1)
Case Is = "Riders"
lr2 = Sheets("Riders").Cells(Rows.Count, "A").End(xlUp).Row
Rows(r).Copy Sheets("Riders").Range("A" & lr2 + 1)
Case Is = "COP"
lr2 = Sheets("COP").Cells(Rows.Count, "A").End(xlUp).Row
Rows(r).Copy Sheets("COP").Range("A" & lr2 + 1)
End Select
Next r
End Sub

 

[Michael M]

Change this line

For r = lr To 1 Step -1

to

For r = 1 To lr