Can you specify an Array as a Target??

[JP]

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]

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]

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]

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]

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]

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.

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

 

[JP]

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]

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

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.