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("A340").Copy Worksheets("Mine").Range("A27")
Range("A27").Select
Else
If Range("e6").Value = "" Then
Range("A2764").Value = ""

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
(E-Mail Removed)
"Marilyn" <(E-Mail Removed)> wrote in message
news5E77658-41EA-4D08-9BC6-(E-Mail Removed)...
> 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("A340").Copy Worksheets("Mine").Range("A27")
> Range("A27").Select
> Else
> If Range("e6").Value = "" Then
> Range("A2764").Value = ""

Hi

You SelectionChange event fire when you select another cell, I suggest you
use Worksheet_Change event, which only fire when a change has been made.

I think this is what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Intersect(Target, [E6])
If Not isect Is Nothing Then
Select Case Target.Value
Case Is = "Contract C"
Worksheets("Sheet3").Range("A340").Copy
Worksheets("Mine").Range("A27")
Range("A27").Select
Case Is = "Contract D"
'What now
Case Is = "Contract E"
'What now
Case Else
Range("A2764").Value = ""
End Select
End If
End Sub

Regards,
Per

"Marilyn" <(E-Mail Removed)> skrev i meddelelsen
news5E77658-41EA-4D08-9BC6-(E-Mail Removed)...
> 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("A340").Copy Worksheets("Mine").Range("A27")
> Range("A27").Select
> Else
> If Range("e6").Value = "" Then
> Range("A2764").Value = ""

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 A340 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
A128169 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
> (E-Mail Removed)
> "Marilyn" <(E-Mail Removed)> wrote in message
> news5E77658-41EA-4D08-9BC6-(E-Mail Removed)...
> > 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("A340").Copy Worksheets("Mine").Range("A27")
> > Range("A27").Select
> > Else
> > If Range("e6").Value = "" Then
> > Range("A2764").Value = ""
>
>

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
(E-Mail Removed)
"Marilyn" <(E-Mail Removed)> wrote in message
news:FF03BF11-CA24-4F42-B122-(E-Mail Removed)...
> 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 A340 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
> A128169 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
>> (E-Mail Removed)
>> "Marilyn" <(E-Mail Removed)> wrote in message
>> news5E77658-41EA-4D08-9BC6-(E-Mail Removed)...
>> > 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("A340").Copy
>> > Worksheets("Mine").Range("A27")
>> > Range("A27").Select
>> > Else
>> > If Range("e6").Value = "" Then
>> > Range("A2764").Value = ""
>>
>>