Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For-Each-Next Freezes Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For-Each-Next Freezes Excel
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. "JLGWhiz" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel freezes | Excel Discussion (Misc queries) | |||
Excel freezes | Excel Discussion (Misc queries) | |||
Excel freezes | Excel Discussion (Misc queries) | |||
Excel freezes | Excel Discussion (Misc queries) | |||
Excel Freezes | Excel Programming |