Copy Info to Another Sheet

[Joek88]

Hi All!

So I have tried VBA and formula and can't seem to get this to work the way I want it to work. Here is what I want this sheet to do:

Column A of sheet "Check Register" (first attached picture) is a droplist with a few options to choose from (Sale of Livestock, Cost of Livestock, Trucking, Custom Hire, Sale of Crops). When I choose one of those options, for example, I would choose "Sale of Livestock" and enter the information into B2 (Number), C2 (Date), D2 (Description of Transaction), and F2 (Credit (+)) based on the information I have in my books. I want it then to copy the values from B2, C2, D2, and F2 of sheet "Check Register" and place them into the sheet named "1a Sale of Livestock". So in the "1a Sale of Livestock" sheet (2nd picture attached) it would show that A13 = value of B2, B13 = value of C2, and so on. As I enter information, I want it to copy the information to different sheets based on what it sees in column A of sheet "Check Register". Basically, IF "Sale of Livestock" is entered, THEN copy the info to ONLY sheet "1a Sale of Livestock", but if "Cost of Livestock" is entered then copy that info to sheet "1b Cost of Livestock" and ONLY that sheet.

If "Sale of Livestock" is entered multiple times into Column A of "Check Register" then just keep adding them like a running list to "1a Sale of Livestock". This would apply to all my dropdown selections.

Can someone please help me on this?

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Check Register" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. This macro is triggered when you select a value in column A. This means that you must enter the data in all the other columns first, and make your selection in column A last. Hopefully, this will work for you.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name Like "*" & Target & "*" Then
            With ws
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 3).Value = Target.Offset(, 1).Resize(, 3).Value
                .Cells(.Rows.Count, "D").End(xlUp).Offset(1).Value = Target.Offset(, 5).Value
            End With
            Exit For
        End If
    Next ws
End Sub

 

[Joek88]

Thank you so much @mumps!! This works exactly as I wanted it to aside from one exception. So I needed to change 2 values. So when I select "Sale of Livestock" AND/OR "Sale of Crops" I need the information in Column F of sheet "Check Register" to be copied to the corresponding sheet. If "Sale of Livestock" shows a value of $1000 in the "Credit (+)" of Column F it would then place that information into Sheet "1a Sale of Livestock" column D. This would be the same for "Sale of Crops". ALL other selections will pull from "Debit (-)" Column E of "Check Register" sheet. Can you please help me with this?

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Select Case Target.Value
        Case "Sale of Livestock", "Sale of Crops"
            For Each ws In Sheets
                If ws.Name Like "*" & Target & "*" Then
                    With ws
                        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 3).Value = Target.Offset(, 1).Resize(, 3).Value
                        If Target.Offset(, 5).Value = 1000 Then
                            .Cells(.Rows.Count, "D").End(xlUp).Offset(1).Value = Target.Offset(, 5).Value
                        End If
                    End With
                    Exit For
                End If
            Next ws
        Case Else
            For Each ws In Sheets
                If ws.Name Like "*" & Target & "*" Then
                    With ws
                        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 3).Value = Target.Offset(, 1).Resize(, 3).Value
                        .Cells(.Rows.Count, "D").End(xlUp).Offset(1).Value = Target.Offset(, 4).Value
                    End With
                    Exit For
                End If
            Next ws
    End Select
    Application.ScreenUpdating = True
End Sub

 

[Joek88]

The code you provided works on everything except, the "Sale of Crops". When "Sale of Crops" is selected from column A of sheet "Check Register" it should copy the information from column F and place that info into column D of sheet "Sale of Crops". This is the only one that is not working correctly.

 

[mumps]

It works properly on my tests. Check the sheet name and the cell in column A to make sure that the string "Sale of Crops" is exactly the same in both without any leading or trailing spaces.

 

[Joek88]

I fixed it! Perfect, thank you so much for your help. This works like intended.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[Joek88]

Oh I see, yes I will do that next time for ease of troubleshooting!

 

[mumps]

If the macro is still not working for you, it would be best to upload a copy of the file.