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?

 

[Joek88]

Ok, I thought I had it fixed. However, I am having issues. I attached the file to see if you can look at it and see what I am missing.

Brad Sheet.xlsm

Shared with Dropbox

www.dropbox.com

 

[mumps]

This works for me:

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, lRow As Long
    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
                            lRow = .Range("A" & Rows.Count).End(xlUp).Row
                            .Range("D" & lRow).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
                        lRow = .Range("A" & Rows.Count).End(xlUp).Row
                        .Range("D" & lRow).Value = Target.Offset(, 4).Valuee
                    End With
                    Exit For
                End If
            Next ws
    End Select
    Application.ScreenUpdating = True
End Sub

 

[Joek88]

Where are you putting this code? I copy and pasted into Sheet1 (Check Register). Is this incorrect? Still can't get it to work properly.

 

[mumps]

It goes into the worksheet code module.

 

[Joek88]

When I select either "Sale of Livestock" or "Sale of Crop" these will be "Credit (+)" only entries in sheet "Check Register". All the information gets transferred to the appropriate sheets EXCEPT the "price" column. Just don't understand what I'm doing wrong. The "Cost of Livestock" works just fine when you select from the "Debit (-)".

SALE OF LIVESTOCK

COST OF LIVESTOCK

 

[mumps]

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".

You said that in order for the price in column F to be transferred to the appropriate sheets, it must have a value of $1000 in columns F. The value in the sample you posted is only $575 so it is not being transferred. Also, make sure you use the version of the macro below.

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, lRow As Long
    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
                            lRow = .Range("A" & Rows.Count).End(xlUp).Row
                            .Range("D" & lRow).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
                        lRow = .Range("A" & Rows.Count).End(xlUp).Row
                        .Range("D" & lRow).Value = Target.Offset(, 4).Valuee
                    End With
                    Exit For
                End If
            Next ws
    End Select
    Application.ScreenUpdating = True
End Sub

 

[Joek88]

mumps, so sorry for the confusion. As soon as I read your reply I knew I told you wrong. The 1000 value was just a value I was throwing out there. I need that value just be greater than 1. I was able to fix it and as soon as you replied I knew exactly why it was not working. I tested it and it works flawlessly!! I want to thank you for your help on this. I could not have figured this out without you. Thank you so much!!!

 

[mumps]

You are very welcome.