Macro Problem

[Brad]

I have a code that creates a quote by moving sheets to a new file. When I
step through the macro, for some reason it is calling upon a
worksheet_selectionchange and causing my virtual memory to go through the
roof. How do I prevent my macro from automatically calling about the
worksheet_selectionchange that I have created. Here is the code that is
causing it to goto the worksheet_selectionchange, along with the code for the
Worksheet_selectionchange. My worksheet_selectionchange is under my
"Agreement" Tab and that is when it gets called upon by the macro. Hope this
makes sense. Any help is appreciated.

AWB.Activate
Sheets("Agreement").Select
Sheets("Agreement").Copy Before:=Workbooks("Email Template
Macro3l.xls").Sheets _
(3)
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Cells.Select

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 52 Then
With Worksheets("Agreement")
promptdialog.Show
End With
End If
End Sub

 

[Dick Kusleika]

Brad

You can use Application.EnableEvents = False at the start of your sub and
then set it to True at the end. That will prevent the selectionchange event
from firing when that particular code is run.

What you might consider, though, is revising your code so that nothing is
selected.

ActiveWorkbook.Sheets("Agreement").Copy Before:=etc...
With Activesheet
.Cells.Copy
.Range("A1").PasteSpecial etc..
End With
Application.CutCopyMode = False

By not selecting in your code, the event won't fire.

 

[Brad]

Dick, thanks for the help, this has solved my problem. My next goal is to
clean up my code. Thanks again!!!!