Copy & Paste Freezing Excel

[Phil Floyd]

I have a workbook with 50+ sheets in it. All but 2 of the sheets are
identical. In all of these identical sheets an employee # will be entered
and some formatting and formulas are added via code. Since most of the
sheets are identical, I put the code in the Workbook_SheetChange. The code
works fine but the problem is when I try to Copy & Paste on any of the
sheets, Excel freezes and I can only exit Excel from the task bar in
Windows. I am prompted to save changes and am able to do so. Below is the
code I am using. Is there some obvious reason why this is happening?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Target, Range("A7:A100")) Is Nothing Then
ActiveCell.Offset(-1, 0).Select
ActiveCell.Range("A1:K1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

....rest of code


Thanks,
Phil

 

[Sharad]

In the code you posted, where is the Copy & Paste code?
All you are doing is remove border line.

Anyway, so the problem occurs when you Copy & Paste, right?
When you Copy and Paste the sheet, again the event Workbook_SheetChange
is fired. So it will run again. May be this is causing the freezing. Try
as under.

In Thisworkbook, General Section define a public variable as under:

Public notAgain As Boolean

Then in the Worksheet_Change add following two lines at TOP!
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If notAgain Then Exit Sub
notAgain = True

'your all code
'then following at the bottom before End Sub
notAgain = False
End Sub

Or if at any place in worksheet_change code you are using exit sub
(except for the one I told above at top)
just above exit sub enter line notAgain = False

Sharad

 

[Phil Floyd]

Sharad,
The copy & paste are all being done manually. There is no copy & paste done
via code. The solution you provided does not work. There is still freezing
of excel when you try to paste. I thought that even though the code is in
the ThisWorkbook section it would only pertain to the range I wanted to use:

If Not Intersect(Target, Range("A7:A100")) Is Nothing Then

Why does the code still fire if anything happens outside that range?
Phil