You would want a worksheet_change event restricted to range("e6") with a
select case block. If desired, send your file to my address below along with
this msg and a clear explanation.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Marilyn" wrote in message
...
Thanks Don
THis is what I want to do
If cell E6 in sheet "Mine" is blank then nothing happens
IF cell e6 = "Contract C " (this is from a user form drop down list)
then go to sheet3 (Which will be hiddden) and copy range A3:D40 and paste
into worksheet "mine" cell A27 - select B24 (I had A 27 in my original)
and
stop

else IF cell e6 = "Contract D " then go to sheet3 and copy range
A128:D169 and paste into worksheet "mine" cell A27 - select B24 (I had A
27
in my original) and stop
repeat for Contract E, Contract F, Contract G, Contract H


"Don Guillett" wrote:

Start at the beginning and tell us what you want to do. If you mean that
you
have ONE cell that changes then you want to put your code in a
worksheet_CHANGE event and restrict to that cell.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Marilyn" wrote in message
...
Hello
Below is my code. It seems to work but... I keep getting an hour
glass
everytime I select A27
I have 7 differenct contracts so I will repeat it using else if
Customer has to be able to alter the range


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("e6").Value = "Contract C" Then
Worksheets("Sheet3").Range("A3:D40").Copy
Worksheets("Mine").Range("A27")
Range("A27").Select
Else
If Range("e6").Value = "" Then
Range("A27:D64").Value = ""