I have a spreadsheet with multiple worksheets. On the Master spreadsheet I
have a column with a validation list giving users a "YES" or "NO" option. If
they select the "YES" option then it copies the data in the target,row cells
49,50,51 and pastes it into another worksheet. By selecting the "NO" option
it will clear the contents of those cells.

The issue that I have is that there will be up to 1000 rows to which an
individual "YES" or "NO" selection will need to be made. To make this faster
I would like to be able to click and drag the "YES" response to multiple
cells where appropriate.

What is the correction I need to make to the following code to allow this to
happen?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Column <> 57 Then Exit Sub
On Error Resume Next
If UCase(Target.Value) = "YES" Then
Application.EnableEvents = False
With ActiveSheet
.Range(.Cells(Target.Row, 49), .Cells(Target.Row, 51)).Copy
Worksheets("Demolition Package 1").Cells(Target.Row - 4,
1).PasteSpecial Paste:=xlPasteValues
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
Application.CutCopyMode = False
End With
Application.EnableEvents = True
End If

Maybe something like this (untested):

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
dim myRngToCheck as range
dim myCell as range
set myrngtocheck = intersect(me.columns(57),target)
if myrngtocheck is nothing then exit sub

for each mycell in myrngtocheck.cells
On Error Resume Next
If UCase(mycell.Value) = "YES" Then
Application.EnableEvents = False
With me 'sheet that owns the code
.Range(.Cells(mycell.Row, 49), .Cells(mycell.Row, 51)).Copy
Worksheets("Demolition Package 1").Cells(mycell.Row - 4, 1) _
.PasteSpecial Paste:=xlPasteValues
end with
Application.EnableEvents = True
End If

.....


JP wrote:
>
> I have a spreadsheet with multiple worksheets. On the Master spreadsheet I
> have a column with a validation list giving users a "YES" or "NO" option. If
> they select the "YES" option then it copies the data in the target,row cells
> 49,50,51 and pastes it into another worksheet. By selecting the "NO" option
> it will clear the contents of those cells.
>
> The issue that I have is that there will be up to 1000 rows to which an
> individual "YES" or "NO" selection will need to be made. To make this faster
> I would like to be able to click and drag the "YES" response to multiple
> cells where appropriate.
>
> What is the correction I need to make to the following code to allow this to
> happen?
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Application.ScreenUpdating = False
> If Target.Column <> 57 Then Exit Sub
> On Error Resume Next
> If UCase(Target.Value) = "YES" Then
> Application.EnableEvents = False
> With ActiveSheet
> .Range(.Cells(Target.Row, 49), .Cells(Target.Row, 51)).Copy
> Worksheets("Demolition Package 1").Cells(Target.Row - 4,
> 1).PasteSpecial Paste:=xlPasteValues
> Do
> If IsEmpty(ActiveCell) = False Then
> ActiveCell.Offset(1, 0).Select
> End If
> Loop Until IsEmpty(ActiveCell) = True
> Application.CutCopyMode = False
> End With
> Application.EnableEvents = True
> End If

--

Dave Peterson

Thanks for your reply Dave... this did not achieve the desired result so I
have sent to you a copy of the spreadsheet and some further information.

Thanks again for your assistance!

JP

"Dave Peterson" wrote:

> Maybe something like this (untested):
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Application.ScreenUpdating = False
> dim myRngToCheck as range
> dim myCell as range
> set myrngtocheck = intersect(me.columns(57),target)
> if myrngtocheck is nothing then exit sub
>
> for each mycell in myrngtocheck.cells
> On Error Resume Next
> If UCase(mycell.Value) = "YES" Then
> Application.EnableEvents = False
> With me 'sheet that owns the code
> .Range(.Cells(mycell.Row, 49), .Cells(mycell.Row, 51)).Copy
> Worksheets("Demolition Package 1").Cells(mycell.Row - 4, 1) _
> .PasteSpecial Paste:=xlPasteValues
> end with
> Application.EnableEvents = True
> End If
>
> .....
>
>
> JP wrote:
> >
> > I have a spreadsheet with multiple worksheets. On the Master spreadsheet I
> > have a column with a validation list giving users a "YES" or "NO" option. If
> > they select the "YES" option then it copies the data in the target,row cells
> > 49,50,51 and pastes it into another worksheet. By selecting the "NO" option
> > it will clear the contents of those cells.
> >
> > The issue that I have is that there will be up to 1000 rows to which an
> > individual "YES" or "NO" selection will need to be made. To make this faster
> > I would like to be able to click and drag the "YES" response to multiple
> > cells where appropriate.
> >
> > What is the correction I need to make to the following code to allow this to
> > happen?
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Application.ScreenUpdating = False
> > If Target.Column <> 57 Then Exit Sub
> > On Error Resume Next
> > If UCase(Target.Value) = "YES" Then
> > Application.EnableEvents = False
> > With ActiveSheet
> > .Range(.Cells(Target.Row, 49), .Cells(Target.Row, 51)).Copy
> > Worksheets("Demolition Package 1").Cells(Target.Row - 4,
> > 1).PasteSpecial Paste:=xlPasteValues
> > Do
> > If IsEmpty(ActiveCell) = False Then
> > ActiveCell.Offset(1, 0).Select
> > End If
> > Loop Until IsEmpty(ActiveCell) = True
> > Application.CutCopyMode = False
> > End With
> > Application.EnableEvents = True
> > End If
>
> --
>
> Dave Peterson
>

I won't open that message.

Please keep the discussion in the newsgroup and describe your question in plain
text.

You'll get the added benefit of lots of readers and lots of potential
responders.

JP wrote:
>
> Thanks for your reply Dave... this did not achieve the desired result so I
> have sent to you a copy of the spreadsheet and some further information.
>
> Thanks again for your assistance!
>
> JP
>
> "Dave Peterson" wrote:
>
> > Maybe something like this (untested):
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Application.ScreenUpdating = False
> > dim myRngToCheck as range
> > dim myCell as range
> > set myrngtocheck = intersect(me.columns(57),target)
> > if myrngtocheck is nothing then exit sub
> >
> > for each mycell in myrngtocheck.cells
> > On Error Resume Next
> > If UCase(mycell.Value) = "YES" Then
> > Application.EnableEvents = False
> > With me 'sheet that owns the code
> > .Range(.Cells(mycell.Row, 49), .Cells(mycell.Row, 51)).Copy
> > Worksheets("Demolition Package 1").Cells(mycell.Row - 4, 1) _
> > .PasteSpecial Paste:=xlPasteValues
> > end with
> > Application.EnableEvents = True
> > End If
> >
> > .....
> >
> >
> > JP wrote:
> > >
> > > I have a spreadsheet with multiple worksheets. On the Master spreadsheet I
> > > have a column with a validation list giving users a "YES" or "NO" option. If
> > > they select the "YES" option then it copies the data in the target,row cells
> > > 49,50,51 and pastes it into another worksheet. By selecting the "NO" option
> > > it will clear the contents of those cells.
> > >
> > > The issue that I have is that there will be up to 1000 rows to which an
> > > individual "YES" or "NO" selection will need to be made. To make this faster
> > > I would like to be able to click and drag the "YES" response to multiple
> > > cells where appropriate.
> > >
> > > What is the correction I need to make to the following code to allow this to
> > > happen?
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Application.ScreenUpdating = False
> > > If Target.Column <> 57 Then Exit Sub
> > > On Error Resume Next
> > > If UCase(Target.Value) = "YES" Then
> > > Application.EnableEvents = False
> > > With ActiveSheet
> > > .Range(.Cells(Target.Row, 49), .Cells(Target.Row, 51)).Copy
> > > Worksheets("Demolition Package 1").Cells(Target.Row - 4,
> > > 1).PasteSpecial Paste:=xlPasteValues
> > > Do
> > > If IsEmpty(ActiveCell) = False Then
> > > ActiveCell.Offset(1, 0).Select
> > > End If
> > > Loop Until IsEmpty(ActiveCell) = True
> > > Application.CutCopyMode = False
> > > End With
> > > Application.EnableEvents = True
> > > End If
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Ok here goes...
There are numerous sheets within the workbook. The first sheet "Revision
Control" contains a master list of drawings required for the project. The
other sheets are drawing packages required for different stages of the
project. The first of these is called "Demolition Package 1".

On the master sheet I have 9 headings for these different packages - so
there is a matrix of drawings and packages. Each drawing can therefore be
selected to appear in up to 9 packages. So I have a drop down list giving
users a "YES" or "NO" option for each drawing under each package.

When a user selects the "YES" option then the information contained within
cells AW, AX and AY is copied to the appropriate Package Sheet. As there are
more Rows at the top of the Master sheet than the Package Sheets I have used
Target.Row -4, 1.

Additionally if a user determines that the drawing is no longer required
within a Package they can select the "NO" option on the Master List and that
information will be cleared from the Package Sheet (Columns A, B & C)

So.. at this stage the code I provided originally will do all of this for me
however I need to go to each cell against each drawing and under each package
and select "YES" individually.... I cannot simply drag the "YES" to other
cells and have the information go to the other sheets.

So I need to be able to click and drag the "YES" or "NO" value both down a
column (for multiple drawings) and across a row (for multiple packages) and
have the information appear on the appropriate Package Sheets.... or
similarly to be able to remove this information by dragging across or down
the "NO" value.

I hope that is clear for you... not easy to explain without you being able
to see it.

thanks

JP


"Dave Peterson" wrote:

> I won't open that message.
>
> Please keep the discussion in the newsgroup and describe your question in plain
> text.
>
> You'll get the added benefit of lots of readers and lots of potential
> responders.
>
> JP wrote:
> >
> > Thanks for your reply Dave... this did not achieve the desired result so I
> > have sent to you a copy of the spreadsheet and some further information.
> >
> > Thanks again for your assistance!
> >
> > JP
> >
> > "Dave Peterson" wrote:
> >
> > > Maybe something like this (untested):
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Application.ScreenUpdating = False
> > > dim myRngToCheck as range
> > > dim myCell as range
> > > set myrngtocheck = intersect(me.columns(57),target)
> > > if myrngtocheck is nothing then exit sub
> > >
> > > for each mycell in myrngtocheck.cells
> > > On Error Resume Next
> > > If UCase(mycell.Value) = "YES" Then
> > > Application.EnableEvents = False
> > > With me 'sheet that owns the code
> > > .Range(.Cells(mycell.Row, 49), .Cells(mycell.Row, 51)).Copy
> > > Worksheets("Demolition Package 1").Cells(mycell.Row - 4, 1) _
> > > .PasteSpecial Paste:=xlPasteValues
> > > end with
> > > Application.EnableEvents = True
> > > End If
> > >
> > > .....
> > >
> > >
> > > JP wrote:
> > > >
> > > > I have a spreadsheet with multiple worksheets. On the Master spreadsheet I
> > > > have a column with a validation list giving users a "YES" or "NO" option. If
> > > > they select the "YES" option then it copies the data in the target,row cells
> > > > 49,50,51 and pastes it into another worksheet. By selecting the "NO" option
> > > > it will clear the contents of those cells.
> > > >
> > > > The issue that I have is that there will be up to 1000 rows to which an
> > > > individual "YES" or "NO" selection will need to be made. To make this faster
> > > > I would like to be able to click and drag the "YES" response to multiple
> > > > cells where appropriate.
> > > >
> > > > What is the correction I need to make to the following code to allow this to
> > > > happen?
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > Application.ScreenUpdating = False
> > > > If Target.Column <> 57 Then Exit Sub
> > > > On Error Resume Next
> > > > If UCase(Target.Value) = "YES" Then
> > > > Application.EnableEvents = False
> > > > With ActiveSheet
> > > > .Range(.Cells(Target.Row, 49), .Cells(Target.Row, 51)).Copy
> > > > Worksheets("Demolition Package 1").Cells(Target.Row - 4,
> > > > 1).PasteSpecial Paste:=xlPasteValues
> > > > Do
> > > > If IsEmpty(ActiveCell) = False Then
> > > > ActiveCell.Offset(1, 0).Select
> > > > End If
> > > > Loop Until IsEmpty(ActiveCell) = True
> > > > Application.CutCopyMode = False
> > > > End With
> > > > Application.EnableEvents = True
> > > > End If
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

First, the code you posted doesn't come close to doing all the things you
describe.

Second, I would never try to do this using events. I think it's doomed for
failure--if there's a typo, if the user turns off events/macros. There are just
too many things that can go wrong (my opinion).

Instead, I would keep all my data in one worksheet. And I'd use
filtering/sorting to show what I want to see.

If I absolutely had to separate the data into multiple sheets, I'd still do all
my updates in one worksheet and create those other sheets on demand--recreate
them each time I needed them. They would be for viewing only--no updates
allowed.



JP wrote:
>
> Ok here goes...
> There are numerous sheets within the workbook. The first sheet "Revision
> Control" contains a master list of drawings required for the project. The
> other sheets are drawing packages required for different stages of the
> project. The first of these is called "Demolition Package 1".
>
> On the master sheet I have 9 headings for these different packages - so
> there is a matrix of drawings and packages. Each drawing can therefore be
> selected to appear in up to 9 packages. So I have a drop down list giving
> users a "YES" or "NO" option for each drawing under each package.
>
> When a user selects the "YES" option then the information contained within
> cells AW, AX and AY is copied to the appropriate Package Sheet. As there are
> more Rows at the top of the Master sheet than the Package Sheets I have used
> Target.Row -4, 1.
>
> Additionally if a user determines that the drawing is no longer required
> within a Package they can select the "NO" option on the Master List and that
> information will be cleared from the Package Sheet (Columns A, B & C)
>
> So.. at this stage the code I provided originally will do all of this for me
> however I need to go to each cell against each drawing and under each package
> and select "YES" individually.... I cannot simply drag the "YES" to other
> cells and have the information go to the other sheets.
>
> So I need to be able to click and drag the "YES" or "NO" value both down a
> column (for multiple drawings) and across a row (for multiple packages) and
> have the information appear on the appropriate Package Sheets.... or
> similarly to be able to remove this information by dragging across or down
> the "NO" value.
>
> I hope that is clear for you... not easy to explain without you being able
> to see it.
>
> thanks
>
> JP
>
> "Dave Peterson" wrote:
>
> > I won't open that message.
> >
> > Please keep the discussion in the newsgroup and describe your question in plain
> > text.
> >
> > You'll get the added benefit of lots of readers and lots of potential
> > responders.
> >
> > JP wrote:
> > >
> > > Thanks for your reply Dave... this did not achieve the desired result so I
> > > have sent to you a copy of the spreadsheet and some further information.
> > >
> > > Thanks again for your assistance!
> > >
> > > JP
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Maybe something like this (untested):
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > Application.ScreenUpdating = False
> > > > dim myRngToCheck as range
> > > > dim myCell as range
> > > > set myrngtocheck = intersect(me.columns(57),target)
> > > > if myrngtocheck is nothing then exit sub
> > > >
> > > > for each mycell in myrngtocheck.cells
> > > > On Error Resume Next
> > > > If UCase(mycell.Value) = "YES" Then
> > > > Application.EnableEvents = False
> > > > With me 'sheet that owns the code
> > > > .Range(.Cells(mycell.Row, 49), .Cells(mycell.Row, 51)).Copy
> > > > Worksheets("Demolition Package 1").Cells(mycell.Row - 4, 1) _
> > > > .PasteSpecial Paste:=xlPasteValues
> > > > end with
> > > > Application.EnableEvents = True
> > > > End If
> > > >
> > > > .....
> > > >
> > > >
> > > > JP wrote:
> > > > >
> > > > > I have a spreadsheet with multiple worksheets. On the Master spreadsheet I
> > > > > have a column with a validation list giving users a "YES" or "NO" option. If
> > > > > they select the "YES" option then it copies the data in the target,row cells
> > > > > 49,50,51 and pastes it into another worksheet. By selecting the "NO" option
> > > > > it will clear the contents of those cells.
> > > > >
> > > > > The issue that I have is that there will be up to 1000 rows to which an
> > > > > individual "YES" or "NO" selection will need to be made. To make this faster
> > > > > I would like to be able to click and drag the "YES" response to multiple
> > > > > cells where appropriate.
> > > > >
> > > > > What is the correction I need to make to the following code to allow this to
> > > > > happen?
> > > > >
> > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > Application.ScreenUpdating = False
> > > > > If Target.Column <> 57 Then Exit Sub
> > > > > On Error Resume Next
> > > > > If UCase(Target.Value) = "YES" Then
> > > > > Application.EnableEvents = False
> > > > > With ActiveSheet
> > > > > .Range(.Cells(Target.Row, 49), .Cells(Target.Row, 51)).Copy
> > > > > Worksheets("Demolition Package 1").Cells(Target.Row - 4,
> > > > > 1).PasteSpecial Paste:=xlPasteValues
> > > > > Do
> > > > > If IsEmpty(ActiveCell) = False Then
> > > > > ActiveCell.Offset(1, 0).Select
> > > > > End If
> > > > > Loop Until IsEmpty(ActiveCell) = True
> > > > > Application.CutCopyMode = False
> > > > > End With
> > > > > Application.EnableEvents = True
> > > > > End If
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Thanks Dave however you are incorrect here. The code DOES do what I need it
to do only that it looks for a single value rather than a range. All I
needed was to use the IF NOT INTERSECT....and it works fine.

Thanks for your feedback though.

"Dave Peterson" wrote:

> First, the code you posted doesn't come close to doing all the things you
> describe.
>
> Second, I would never try to do this using events. I think it's doomed for
> failure--if there's a typo, if the user turns off events/macros. There are just
> too many things that can go wrong (my opinion).
>
> Instead, I would keep all my data in one worksheet. And I'd use
> filtering/sorting to show what I want to see.
>
> If I absolutely had to separate the data into multiple sheets, I'd still do all
> my updates in one worksheet and create those other sheets on demand--recreate
> them each time I needed them. They would be for viewing only--no updates
> allowed.
>
>
>
> JP wrote:
> >
> > Ok here goes...
> > There are numerous sheets within the workbook. The first sheet "Revision
> > Control" contains a master list of drawings required for the project. The
> > other sheets are drawing packages required for different stages of the
> > project. The first of these is called "Demolition Package 1".
> >
> > On the master sheet I have 9 headings for these different packages - so
> > there is a matrix of drawings and packages. Each drawing can therefore be
> > selected to appear in up to 9 packages. So I have a drop down list giving
> > users a "YES" or "NO" option for each drawing under each package.
> >
> > When a user selects the "YES" option then the information contained within
> > cells AW, AX and AY is copied to the appropriate Package Sheet. As there are
> > more Rows at the top of the Master sheet than the Package Sheets I have used
> > Target.Row -4, 1.
> >
> > Additionally if a user determines that the drawing is no longer required
> > within a Package they can select the "NO" option on the Master List and that
> > information will be cleared from the Package Sheet (Columns A, B & C)
> >
> > So.. at this stage the code I provided originally will do all of this for me
> > however I need to go to each cell against each drawing and under each package
> > and select "YES" individually.... I cannot simply drag the "YES" to other
> > cells and have the information go to the other sheets.
> >
> > So I need to be able to click and drag the "YES" or "NO" value both down a
> > column (for multiple drawings) and across a row (for multiple packages) and
> > have the information appear on the appropriate Package Sheets.... or
> > similarly to be able to remove this information by dragging across or down
> > the "NO" value.
> >
> > I hope that is clear for you... not easy to explain without you being able
> > to see it.
> >
> > thanks
> >
> > JP
> >
> > "Dave Peterson" wrote:
> >
> > > I won't open that message.
> > >
> > > Please keep the discussion in the newsgroup and describe your question in plain
> > > text.
> > >
> > > You'll get the added benefit of lots of readers and lots of potential
> > > responders.
> > >
> > > JP wrote:
> > > >
> > > > Thanks for your reply Dave... this did not achieve the desired result so I
> > > > have sent to you a copy of the spreadsheet and some further information.
> > > >
> > > > Thanks again for your assistance!
> > > >
> > > > JP
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Maybe something like this (untested):
> > > > >
> > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > Application.ScreenUpdating = False
> > > > > dim myRngToCheck as range
> > > > > dim myCell as range
> > > > > set myrngtocheck = intersect(me.columns(57),target)
> > > > > if myrngtocheck is nothing then exit sub
> > > > >
> > > > > for each mycell in myrngtocheck.cells
> > > > > On Error Resume Next
> > > > > If UCase(mycell.Value) = "YES" Then
> > > > > Application.EnableEvents = False
> > > > > With me 'sheet that owns the code
> > > > > .Range(.Cells(mycell.Row, 49), .Cells(mycell.Row, 51)).Copy
> > > > > Worksheets("Demolition Package 1").Cells(mycell.Row - 4, 1) _
> > > > > .PasteSpecial Paste:=xlPasteValues
> > > > > end with
> > > > > Application.EnableEvents = True
> > > > > End If
> > > > >
> > > > > .....
> > > > >
> > > > >
> > > > > JP wrote:
> > > > > >
> > > > > > I have a spreadsheet with multiple worksheets. On the Master spreadsheet I
> > > > > > have a column with a validation list giving users a "YES" or "NO" option. If
> > > > > > they select the "YES" option then it copies the data in the target,row cells
> > > > > > 49,50,51 and pastes it into another worksheet. By selecting the "NO" option
> > > > > > it will clear the contents of those cells.
> > > > > >
> > > > > > The issue that I have is that there will be up to 1000 rows to which an
> > > > > > individual "YES" or "NO" selection will need to be made. To make this faster
> > > > > > I would like to be able to click and drag the "YES" response to multiple
> > > > > > cells where appropriate.
> > > > > >
> > > > > > What is the correction I need to make to the following code to allow this to
> > > > > > happen?
> > > > > >
> > > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > > Application.ScreenUpdating = False
> > > > > > If Target.Column <> 57 Then Exit Sub
> > > > > > On Error Resume Next
> > > > > > If UCase(Target.Value) = "YES" Then
> > > > > > Application.EnableEvents = False
> > > > > > With ActiveSheet
> > > > > > .Range(.Cells(Target.Row, 49), .Cells(Target.Row, 51)).Copy
> > > > > > Worksheets("Demolition Package 1").Cells(Target.Row - 4,
> > > > > > 1).PasteSpecial Paste:=xlPasteValues
> > > > > > Do
> > > > > > If IsEmpty(ActiveCell) = False Then
> > > > > > ActiveCell.Offset(1, 0).Select
> > > > > > End If
> > > > > > Loop Until IsEmpty(ActiveCell) = True
> > > > > > Application.CutCopyMode = False
> > > > > > End With
> > > > > > Application.EnableEvents = True
> > > > > > End If
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

I didn't see anything in your posted code that processed the "NO" portion.



JP wrote:
>
> Thanks Dave however you are incorrect here. The code DOES do what I need it
> to do only that it looks for a single value rather than a range. All I
> needed was to use the IF NOT INTERSECT....and it works fine.
>
> Thanks for your feedback though.
>
> "Dave Peterson" wrote:
>
> > First, the code you posted doesn't come close to doing all the things you
> > describe.
> >
> > Second, I would never try to do this using events. I think it's doomed for
> > failure--if there's a typo, if the user turns off events/macros. There are just
> > too many things that can go wrong (my opinion).
> >
> > Instead, I would keep all my data in one worksheet. And I'd use
> > filtering/sorting to show what I want to see.
> >
> > If I absolutely had to separate the data into multiple sheets, I'd still do all
> > my updates in one worksheet and create those other sheets on demand--recreate
> > them each time I needed them. They would be for viewing only--no updates
> > allowed.
> >
> >
> >
> > JP wrote:
> > >
> > > Ok here goes...
> > > There are numerous sheets within the workbook. The first sheet "Revision
> > > Control" contains a master list of drawings required for the project. The
> > > other sheets are drawing packages required for different stages of the
> > > project. The first of these is called "Demolition Package 1".
> > >
> > > On the master sheet I have 9 headings for these different packages - so
> > > there is a matrix of drawings and packages. Each drawing can therefore be
> > > selected to appear in up to 9 packages. So I have a drop down list giving
> > > users a "YES" or "NO" option for each drawing under each package.
> > >
> > > When a user selects the "YES" option then the information contained within
> > > cells AW, AX and AY is copied to the appropriate Package Sheet. As there are
> > > more Rows at the top of the Master sheet than the Package Sheets I have used
> > > Target.Row -4, 1.
> > >
> > > Additionally if a user determines that the drawing is no longer required
> > > within a Package they can select the "NO" option on the Master List and that
> > > information will be cleared from the Package Sheet (Columns A, B & C)
> > >
> > > So.. at this stage the code I provided originally will do all of this for me
> > > however I need to go to each cell against each drawing and under each package
> > > and select "YES" individually.... I cannot simply drag the "YES" to other
> > > cells and have the information go to the other sheets.
> > >
> > > So I need to be able to click and drag the "YES" or "NO" value both down a
> > > column (for multiple drawings) and across a row (for multiple packages) and
> > > have the information appear on the appropriate Package Sheets.... or
> > > similarly to be able to remove this information by dragging across or down
> > > the "NO" value.
> > >
> > > I hope that is clear for you... not easy to explain without you being able
> > > to see it.
> > >
> > > thanks
> > >
> > > JP
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > I won't open that message.
> > > >
> > > > Please keep the discussion in the newsgroup and describe your question in plain
> > > > text.
> > > >
> > > > You'll get the added benefit of lots of readers and lots of potential
> > > > responders.
> > > >
> > > > JP wrote:
> > > > >
> > > > > Thanks for your reply Dave... this did not achieve the desired result so I
> > > > > have sent to you a copy of the spreadsheet and some further information.
> > > > >
> > > > > Thanks again for your assistance!
> > > > >
> > > > > JP
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > Maybe something like this (untested):
> > > > > >
> > > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > > Application.ScreenUpdating = False
> > > > > > dim myRngToCheck as range
> > > > > > dim myCell as range
> > > > > > set myrngtocheck = intersect(me.columns(57),target)
> > > > > > if myrngtocheck is nothing then exit sub
> > > > > >
> > > > > > for each mycell in myrngtocheck.cells
> > > > > > On Error Resume Next
> > > > > > If UCase(mycell.Value) = "YES" Then
> > > > > > Application.EnableEvents = False
> > > > > > With me 'sheet that owns the code
> > > > > > .Range(.Cells(mycell.Row, 49), .Cells(mycell.Row, 51)).Copy
> > > > > > Worksheets("Demolition Package 1").Cells(mycell.Row - 4, 1) _
> > > > > > .PasteSpecial Paste:=xlPasteValues
> > > > > > end with
> > > > > > Application.EnableEvents = True
> > > > > > End If
> > > > > >
> > > > > > .....
> > > > > >
> > > > > >
> > > > > > JP wrote:
> > > > > > >
> > > > > > > I have a spreadsheet with multiple worksheets. On the Master spreadsheet I
> > > > > > > have a column with a validation list giving users a "YES" or "NO" option. If
> > > > > > > they select the "YES" option then it copies the data in the target,row cells
> > > > > > > 49,50,51 and pastes it into another worksheet. By selecting the "NO" option
> > > > > > > it will clear the contents of those cells.
> > > > > > >
> > > > > > > The issue that I have is that there will be up to 1000 rows to which an
> > > > > > > individual "YES" or "NO" selection will need to be made. To make this faster
> > > > > > > I would like to be able to click and drag the "YES" response to multiple
> > > > > > > cells where appropriate.
> > > > > > >
> > > > > > > What is the correction I need to make to the following code to allow this to
> > > > > > > happen?
> > > > > > >
> > > > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > > > Application.ScreenUpdating = False
> > > > > > > If Target.Column <> 57 Then Exit Sub
> > > > > > > On Error Resume Next
> > > > > > > If UCase(Target.Value) = "YES" Then
> > > > > > > Application.EnableEvents = False
> > > > > > > With ActiveSheet
> > > > > > > .Range(.Cells(Target.Row, 49), .Cells(Target.Row, 51)).Copy
> > > > > > > Worksheets("Demolition Package 1").Cells(Target.Row - 4,
> > > > > > > 1).PasteSpecial Paste:=xlPasteValues
> > > > > > > Do
> > > > > > > If IsEmpty(ActiveCell) = False Then
> > > > > > > ActiveCell.Offset(1, 0).Select
> > > > > > > End If
> > > > > > > Loop Until IsEmpty(ActiveCell) = True
> > > > > > > Application.CutCopyMode = False
> > > > > > > End With
> > > > > > > Application.EnableEvents = True
> > > > > > > End If
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson