Manipulating Sheet 2 using a master Sheet

peashoe

New Member
Joined
Aug 16, 2007
Messages
3
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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Put this code in the WorkSheet Module for that worksheet.
Code:
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

New Member
Joined
Aug 16, 2007
Messages
3
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
Code:
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

New Member
Joined
Aug 16, 2007
Messages
3
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,368
Messages
5,595,754
Members
414,017
Latest member
surajks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top