copy and paste VBA repeats and repeats
 

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

copy and paste VBA repeats and repeats
 

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

copy and paste VBA repeats and repeats
 

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("A3:D40").Copy
Worksheets("Mine").Range("A27")
Range("A27").Select
Case Is = "Contract D"
'What now
Case Is = "Contract E"
'What now
Case Else
Range("A27:D64").Value = ""
End Select
End If
End Sub

Regards,
Per

"Marilyn" skrev i meddelelsen
...
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 = ""

copy and paste VBA repeats and repeats
 

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

copy and paste VBA repeats and repeats
 

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