Actually, I bet that your procedure is running nicely.

If you paste a single cell into A1, you'll see that the other stuff gets run.

But if you paste over a bunch of cells, then this line:

if target.address = "$A$1" then

with not be what you want. The Target.Address will be something like
$A$1:$C$99--multiple cells--so the stuff under the "then" portion of your code
doesn't run.

Since you're only using A1, you could modify it to something like:

Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range
Set myCell = Me.Range("A1")

If Intersect(Target, myCell) Is Nothing Then
Exit Sub
Else
With Worksheets("Sheet2")
.Rows("1:35").EntireRow.Hidden = False
If myCell.Text = "Apples" Then
.Rows("10:10").EntireRow.Hidden = True
.Rows("20:20").EntireRow.Hidden = True
ElseIf myCell.Text = "Pears" Then
.Rows("15:15").EntireRow.Hidden = True
.Rows("25:25").EntireRow.Hidden = True
ElseIf myCell.Text = "Oranges" Then
.Rows("30:30").EntireRow.Hidden = True
.Rows("35:35").EntireRow.Hidden = True
Else
.Rows("12:12").EntireRow.Hidden = True
.Rows("16:16").EntireRow.Hidden = True
End If
End With
End If
End Sub

Note that I added another "Option" statement. Then Pears, PEARS, PEarS, pears,
.... will all be treated the same.

======
One more thing...

If your code that does the copy turns off events:
application.enableevents = false
'code that copies|pastes
application.enableevents = true

Then this event won't fire. If you want to have the code run, then stay away
from disabling events -- or call this procedure -- or do the same kind of thing
in your copy|paste routine.


AMY Z. wrote:

Hi,
The procedure below works great if I manually enter a value in A1. I have
another macro that copies and pastes a certain row into row1. The procedure
below won't change if I run the copy and paste macro, even though A1 has
changed.
Is there a way around this so the code below recognizes a change by copy and
pasting?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
With Worksheets("Sheet2")
.Rows("1:35").EntireRow.Hidden = False
If Target.Text = "Apples" Then
.Rows("10:10").EntireRow.Hidden = True
.Rows("20:20").EntireRow.Hidden = True
ElseIf Target.Text = "Pears" Then
.Rows("15:15").EntireRow.Hidden = True
.Rows("25:25").EntireRow.Hidden = True
ElseIf Target.Text = "Oranges" Then
.Rows("30:30").EntireRow.Hidden = True
.Rows("35:35").EntireRow.Hidden = True
Else
.Rows("12:12").EntireRow.Hidden = True
.Rows("16:16").EntireRow.Hidden = True
End If
End With
End If
End Sub

I hope I explained so you can understand.
Thank you your time in advance,
Amy

--

Dave Peterson

Thanks Dave for taking time to rewrite my code, It Works!
Thank you also JNW and Gary for the tips.
Amy


"Dave Peterson" wrote:

Actually, I bet that your procedure is running nicely.

If you paste a single cell into A1, you'll see that the other stuff gets run.

But if you paste over a bunch of cells, then this line:

if target.address = "$A$1" then

with not be what you want. The Target.Address will be something like
$A$1:$C$99--multiple cells--so the stuff under the "then" portion of your code
doesn't run.

Since you're only using A1, you could modify it to something like:

Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range
Set myCell = Me.Range("A1")

If Intersect(Target, myCell) Is Nothing Then
Exit Sub
Else
With Worksheets("Sheet2")
.Rows("1:35").EntireRow.Hidden = False
If myCell.Text = "Apples" Then
.Rows("10:10").EntireRow.Hidden = True
.Rows("20:20").EntireRow.Hidden = True
ElseIf myCell.Text = "Pears" Then
.Rows("15:15").EntireRow.Hidden = True
.Rows("25:25").EntireRow.Hidden = True
ElseIf myCell.Text = "Oranges" Then
.Rows("30:30").EntireRow.Hidden = True
.Rows("35:35").EntireRow.Hidden = True
Else
.Rows("12:12").EntireRow.Hidden = True
.Rows("16:16").EntireRow.Hidden = True
End If
End With
End If
End Sub

Note that I added another "Option" statement. Then Pears, PEARS, PEarS, pears,
.... will all be treated the same.

======
One more thing...

If your code that does the copy turns off events:
application.enableevents = false
'code that copies|pastes
application.enableevents = true

Then this event won't fire. If you want to have the code run, then stay away
from disabling events -- or call this procedure -- or do the same kind of thing
in your copy|paste routine.


AMY Z. wrote:

Hi,
The procedure below works great if I manually enter a value in A1. I have
another macro that copies and pastes a certain row into row1. The procedure
below won't change if I run the copy and paste macro, even though A1 has
changed.
Is there a way around this so the code below recognizes a change by copy and
pasting?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
With Worksheets("Sheet2")
.Rows("1:35").EntireRow.Hidden = False
If Target.Text = "Apples" Then
.Rows("10:10").EntireRow.Hidden = True
.Rows("20:20").EntireRow.Hidden = True
ElseIf Target.Text = "Pears" Then
.Rows("15:15").EntireRow.Hidden = True
.Rows("25:25").EntireRow.Hidden = True
ElseIf Target.Text = "Oranges" Then
.Rows("30:30").EntireRow.Hidden = True
.Rows("35:35").EntireRow.Hidden = True
Else
.Rows("12:12").EntireRow.Hidden = True
.Rows("16:16").EntireRow.Hidden = True
End If
End With
End If
End Sub

I hope I explained so you can understand.
Thank you your time in advance,
Amy

--

Dave Peterson

.Rows("1:35").EntireRow.Hidden = False
.Rows("10:10").EntireRow.Hidden = True
.Rows("20:20").EntireRow.Hidden = True

Just an alternative:

.Rows("1:35").Hidden = False
.Rows(10).Hidden = True
.Rows(20).Hidden = True

--
Dana DeLouis
Windows XP & Office 2003


"AMY Z." wrote in message
...
Thanks Dave for taking time to rewrite my code, It Works!
Thank you also JNW and Gary for the tips.
Amy

<snip

or
Sub hiderows()
.Rows("1:35").Hidden = False

.Range("a4,a10").EntireRow.Hidden = True
End Sub

--
Don Guillett
SalesAid Software

"Dana DeLouis" wrote in message
...
.Rows("1:35").EntireRow.Hidden = False
.Rows("10:10").EntireRow.Hidden = True
.Rows("20:20").EntireRow.Hidden = True

Just an alternative:

.Rows("1:35").Hidden = False
.Rows(10).Hidden = True
.Rows(20).Hidden = True

--
Dana DeLouis
Windows XP & Office 2003


"AMY Z." wrote in message
...
Thanks Dave for taking time to rewrite my code, It Works!
Thank you also JNW and Gary for the tips.
Amy

<snip