copy and paste VBA repeats and repeats

[Marilyn]

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 = ""

 

[Don Guillett]

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.

 

[Per Jessen]

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]

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]

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.