VBA to copy data from selected cells when one criterion is met

[lafleuk]

Hello-

I am new to the forum and to VBA, and have been searching to no avail for the correct code for my spreadsheet. I have data in a sheet called "Oversight Activities 2012-2013" with data validation. I will be inputting data into this sheet. When "A" is chosen in column A, I would like certain columns to populate under the appropriate headings in the sheet called "Reports" which contains only some of the same column headings and not in the same order.

I have attached the spreadsheet for your perusal. Can anyone assist please? I've zero experience with VBA. Thanks in advance

[jaslake]

Hi lafleuk

Place this code in the Sheet Module of "Oversight Activities 2012-2013". Right Click on the Tab ----> View Code. Paste this code in the Right Hand Panel. Let me know of issues.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NR As Long
    Dim ws As Worksheet

    Set ws = Sheets("Report")
    If Not Target.Column = 1 Then Exit Sub
    If Not Target.Row >= 5 Then Exit Sub
    If Cells(Target.Row, Target.Column).Value = "A" Then
        Application.ScreenUpdating = False
        With ws
            NR = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious).Row + 1
            .Cells(NR, 1).Value = Cells(Target.Row, 10).Value
            .Cells(NR, 2).Value = Cells(Target.Row, 7).Value
            .Cells(NR, 3).Value = Cells(Target.Row, 2).Value
            .Cells(NR, 4).Value = Cells(Target.Row, 11).Value
            .Cells(NR, 5).Value = Cells(Target.Row, 8).Value
            .Cells(NR, 6).Value = Cells(Target.Row, 4).Value
            .Cells(NR, 7).Value = Cells(Target.Row, 5).Value
            .Cells(NR, 8).Value = Cells(Target.Row, 6).Value
            .Cells(NR, 9).Value = Cells(Target.Row, 15).Value
            .Cells(NR, 10).Value = Cells(Target.Row, 17).Value
            .Cells(NR, 11).Value = Cells(Target.Row, 18).Value
        End With
        Application.ScreenUpdating = True
    End If
End Sub

[lafleuk]

Hi John,
Thanks so much for this, I cannot seem to get it to run though. It does not seem to be recognizing it as a macro...I have tried to run it from the VBA page and also tried using the macro-run command from the spreadsheet page, but the macros in this workbook window is blank. Any idea why?
Kara

[jaslake]

Hi lafleuk

Right Click on the Tab for "Oversight Activities 2012-2013" Click on tab.jpg

Select "View Code" and you'll see this screen VBA Screen.jpg

Paste the Code into this screen Paste Code.jpg

Fill in all the data for a line on "Oversight Activities 2012-2013" EXCEPT Column A. When all data is complete put an "A" in Column A. The Code will run as a result of Column A being an "A".

[lafleuk]

Yippeeee! Thanks so much for this John! I was puzzling over this one for a long while , am very grateful for your response and it works just as it should. Have a great day! Kara

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.