help with a code

[Guest]

here's a part of the code i have for Sheet 1. In the
middle i want to go to the next sheet, do some things
there and come back to sheet 1. This code loops itself
back to the beginning from the step just before the go to
sheet 2 [Sheets("Parts List").Select]. can anyone help me
figuring out what am i missing here? thanks

I made a macro only with the Else statement and that works
fine.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A:A")) Is Nothing Then
Exit Sub
Else

Columns("F:F").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteAll,
Operation:=xlAdd, SkipBlanks:= _
False, Transpose:=False
Sheets("Parts List").Select
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteAll,
Operation:=xlAdd, SkipBlanks:= _
False, Transpose:=False
Range("A1:C1").Font.Bold = True

Range("A1").Select

 

[Township of East Hanover]

Ofcourse it does because by pasting in you have changed the worksheet
therefore the worksheet_change gets called again.

 

[Frank Kabel]

Hi
add the line
Application.enableevents = False
at the beginning of your code and the line
Application.enableevents = True
at the end of your code

Otherwise your cell change triggers the worksheet_change event again

 

[Bob Phillips]

Is it going into oblivion?

Try this

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False
If Intersect(Target, Range("A:A")) Is Nothing Then
Exit Sub
Else
Columns("F:F").Copy
Columns("A:A").PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd,
SkipBlanks:= _
False, Transpose:=False
Sheets("Parts List").Select
Columns("B:B").PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd,
SkipBlanks:= _
False, Transpose:=False
Range("A1:C1").Font.Bold = True
Me.Select
Range("A1").Select
End If
ws_exit:
Application.EnableEvents = True
End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

THanks a lot everyone

 

[AxeldraX]

before you go to the next sheet you make a change in sheet1
Selection.PasteSpecial Paste:=xlPasteAll,

Operation:=xlAdd, SkipBlanks:= _
False, Transpose:=False

so the event starts again and again:rolleyes