Manipulating Sheet 2 using a master Sheet

[peashoe]

Hi!,
I am completely new to excel programming but I am a VB & C# programmer. I have a client that needs the following requirements:

If I have a master sheet that has the following information:

ISBN Title Units Costs Move to LOS (Y/N)
1111 A 1 $10
2222 B 5 $20
3333 C 2 $15

is there a way to program the excel sheet so that if a user needs the entire row for ISBNs 1111 & 3333 (for example) to copy to a separate sheet, all they would need to do is put a "Y" under the "Move to LOS" column. Which would copy the following info on sheet 1 automatically?

ISBN Title Units Costs
1111 A 1 $10
3333 C 2 $15


Thanks in advance for any help
Lisa

 

[Datsmart]

Put this code in the WorkSheet Module for that worksheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim VRange As Range, cell As Range
    Set VRange = Range("E2:E4")
    For Each cell In Target
        If Union(cell, VRange).Address = VRange.Address Then
            If cell.Value = "Y" Then
            EndRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Sheets("Sheet2").Cells(EndRow, 1).Value = cell.Offset(0, -4).Value
                Sheets("Sheet2").Cells(EndRow, 2).Value = cell.Offset(0, -3).Value
                Sheets("Sheet2").Cells(EndRow, 3).Value = cell.Offset(0, -2).Value
                Sheets("Sheet2").Cells(EndRow, 4).Value = cell.Offset(0, -1).Value
            End If
        End If
    Next cell
End Sub

How to put code in WorkSheet Module:
Right click sheet tab
Click "View Code"
Paste the code into the panel that opens. ("WorkSheet" Module)
Alt-Q to quit the VBA Editor.

 

[peashoe]

John,
That worked beautifully!!!! Thank you so much! Just one more twist that I would like to ask. If they decide that they want different ISBN rows to go to different sheets, how would I do that?

Example: (where there is an LOB, SAS & Bid sheet and there could be thousands of records

Master Sheet would have:
ISBN, Title, Units, LOB (Y/N), SAS (Y/N), Bid (Y/N)

If they put Y for LOB for some records and SAS for others - then all those records would need to go to that specific sheet, etc.....Do I just need to create a Sub for each worksheet_change?

 

[Datsmart]

You can only have one WorkSheet_Change event per worksheet.
But you can add other steps to the code.

Lets look at the first line of code that assigns values when a "Y" is found.

Sheets("Sheet2").Cells(EndRow, 1).Value = cell.Offset(0, -4).Value

Think of that line as "Target = Source"
The Target part:
"Sheets("Sheet2").Cells(EndRow, 1).Value"
This sets the Target cell to receive the value.
("Sheet2") is the worksheet name.
Cells(EndRow, 1) is the cell range.
EndRow is a variable set earlier in the code to be the lastrow, plus one, of the target worksheet, so it will not over-write data.
So if EndRow = 6, then Cells(EndRow, 1) would read, "Cells(6, 1)". Which would be the cell in Row 6, Column 1, or A6.

The Source part:
"cell.Offset(0, -4).Value"
This part of the line assigns the Source value found 4 columns to the left of the found "Y".
"cell" is the range where a "Y" is found, say cell E2.
"Offset" tells the code to choose a range adjacent to the found cell so many cells right/left and up/down from that cell.
"Offset(0, -4)" says 0 rows up/down and 4 columns left, or cell A2.
If the Y is in column E (Column 5), then the Source cell would be Column A, Column 5 - 4)

I hope this helps you set other lines of code to copy what you want.

 

[peashoe]

John,
makes perfect sense - this is what I came up with and it works beautifully - thanks so much for your help!

For Each cell In Target

If Union(cell, VRange1).Address = VRange1.Address Then
If VRange1.Address = "$E$2:$E$9" Then
If cell.Value = "Y" Then
EndRow = Sheets("LOB").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("LOB").Cells(EndRow, 1).Value = cell.Offset(0, -4).Value
Sheets("LOB").Cells(EndRow, 2).Value = cell.Offset(0, -3).Value
Sheets("LOB").Cells(EndRow, 3).Value = cell.Offset(0, -2).Value
Sheets("LOB").Cells(EndRow, 4).Value = cell.Offset(0, -1).Value

End If
End If
ElseIf VRange2.Address = "$F$2:$F$9" Then
If cell.Value = "Y" Then
EndRow = Sheets("SAS").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("SAS").Cells(EndRow, 1).Value = cell.Offset(0, -5).Value
Sheets("SAS").Cells(EndRow, 2).Value = cell.Offset(0, -4).Value
Sheets("SAS").Cells(EndRow, 3).Value = cell.Offset(0, -3).Value
Sheets("SAS").Cells(EndRow, 4).Value = cell.Offset(0, -2).Value
Sheets("SAS").Cells(EndRow, 5).Value = cell.Offset(0, -1).Value

End If
End If

Next cell

 

[Datsmart]

Nicely done!

I couldn't get your new code to run on my test workbook.

Not sure about your code on two lines. The "If VRange1.Address = "$E$2:$E$9" Then" and "ElseIf VRange2.Address = "$F$2:$F$9" Then" statements don't make sense.
I revised it some like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim VRange1 As Range, VRange2 As Range, cell As Range
    Set VRange1 = Range("E2:E4")
    Set VRange2 = Range("F2:F4")
    
For Each cell In Target
    If Union(cell, VRange1).Address = VRange1.Address Then
            If cell.Value = "Y" Then
                EndRow = Sheets("LOB").Cells(Rows.Count, "A").End(xlUp).Row + 1
                Sheets("LOB").Cells(EndRow, 1).Value = cell.Offset(0, -4).Value
                Sheets("LOB").Cells(EndRow, 2).Value = cell.Offset(0, -3).Value
                Sheets("LOB").Cells(EndRow, 3).Value = cell.Offset(0, -2).Value
                Sheets("LOB").Cells(EndRow, 4).Value = cell.Offset(0, -1).Value
            End If
    ElseIf Union(cell, VRange2).Address = VRange2.Address Then
        If cell.Value = "Y" Then
            EndRow = Sheets("SAS").Cells(Rows.Count, "A").End(xlUp).Row + 1
            Sheets("SAS").Cells(EndRow, 1).Value = cell.Offset(0, -5).Value
            Sheets("SAS").Cells(EndRow, 2).Value = cell.Offset(0, -4).Value
            Sheets("SAS").Cells(EndRow, 3).Value = cell.Offset(0, -3).Value
            Sheets("SAS").Cells(EndRow, 4).Value = cell.Offset(0, -2).Value
            Sheets("SAS").Cells(EndRow, 5).Value = cell.Offset(0, -1).Value
        End If
    End If
Next cell
End Sub