# Manipulating Sheet 2 using a master Sheet

#### peashoe

##### New Member
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

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Datsmart

##### Well-known Member
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 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
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
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
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 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
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 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
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``````

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,138
Messages
5,835,619
Members
430,371
Latest member
contentment

### 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?

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