For-Each-Next Freezes Excel

[J. Cornor]

I have 2 workbooks open; a source book and a destination book. The source
book has several worksheets where I select several rows of data from each
worksheet to copy and paste to the destination book. The code I use to do
this is as follows:
( It does exactly what it is supposed to do, perfectly... but when the code
is done and I close the source workbook, the destination book is froze and if
I try to click in the spreadsheet I get one of those "Excel has experienced
some problem" and forces me to close it) What am I missing in my code to
prevent this?

Sub Append()
Application.ScreenUpdating = False
Application.EnableEvents = False

'Prepare destination workbook to receive selected rows of data.
PrepDestBook

'Check each worksheet for selected items.
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
If ws.Range("CheckedBoxes").Value > 0 Then ws.Select

'Copy each selected item and paste them in the destination workbook.
GetItemsAndAppend

'Reset selected items to unselected.
ResetLinks

Next ws

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub PrepDestBook()
Dim x As Integer
Dim y As Integer

y = Worksheets(1).Range("M3").Value

ActiveWindow.ActivateNext
Range("RowInsertPoint").Select
x = ActiveCell.Row
If Range("A6").Value < 1 Then
Range(x & ":" & y + x - 2).EntireRow.Insert
Else: Range(x & ":" & y + x - 1).EntireRow.Insert
End If
x = 0
y = 0
ActiveWindow.ActivateNext
End Sub

Sub GetItemsAndAppend()
Dim Cel As Range
For Each Cel In Range("CheckBoxLinks")
If Cel.Value = True Then
Cel.Offset(0, -12).Range("A1:K1").Copy

ActiveWindow.ActivateNext
Range("RowInsertPoint").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
ActiveWindow.ActivateNext
End If
Next Cel
End Sub

Sub ResetLinks()
Dim Cel As Range
For Each Cel In Range("CheckBoxLinks")
If Cel.Value = True Then
Cel.Value = False
End If
Next Cel
End Sub

Your help is much appreciated,
Thank You

 

[JLGWhiz]

I haven't quite figured out why yet, but I suspect it is the
ActiveWindow.ActivateNext statement that is causing the hangup when you close
the source wb. The other book is lost somewhere in the z order. I would
have to set up a model to be sure about it, or you could step through a short
cycle and see where the focus is when you close the source book. That might
tell you. I don't see anything else that would cause it to sleep.

 

[J. Cornor]

Thank you for your interest and response JLG.
I stepped through it, rewrote it, stepped through it, rewrote it, etc., etc.
If I left the source book open when the macros finished and switched to
the destination book, everything would be fine, but close the source book
and instant freeze. I completely eliminated all For loops, reconstructed
commands to reduce the ActiveWindow.ActivateNext actions to two, did
everything I could think of and no matter what, the results were always
the same. ( by the way... waiting a few seconds and pressing [F9] would
free it. Don't understand it, but it freed it. the book is set to
autocalculate )
I have been trying to solve this problem for two weeks when I finally gave
up and posted here. Talk about FRUSTRATION.
I inadvertantly solved the problem today by adding a command in a BeforeClose
Sub in the source book's "ThisWorkbook" code module to open another book
setup to be an index book which would allow me to choose other workbooks
to open if I needed to before returning to the destination book. Whether I
chose
to open another book from there or not when I closed that book everything was
fine. Sorry for being a chatter box. Sorry for not having a clue as to why it
happened... I'll be working on that for a long time.
Thanks again.