Workbook Stop Working When Run VBA

macropod · #1 12-01-2016, 04:03 AM

I don't have time to analyze all your code. However, your SummurizeSheets macro could be made much simpler and more efficient:

Code:

Sub SummarizeSheets()
Application.ScreenUpdating = False
Dim wsSrc As Worksheet, wsTgt As Worksheet, lRow As Long
Set wsTgt = Worksheets("Monthly_Report")
lRow = wsTgt.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
For Each wsSrc In Worksheets
  With wsSrc
    If .Name <> "Monthly_Report" And .Name <> "Master_Sheet" And .Name <> "Default" Then
      wsTgt.Cells(lRow, 6).Value = .Range("B14")
      wsTgt.Cells(lRow, 7).Value = .Range("C14")
      wsTgt.Cells(lRow, 8).Value = .Range("B2")
      wsTgt.Cells(lRow, 9).Value = .Range("B4")
      wsTgt.Cells(lRow, 10).Value = .Range("D4")
      wsTgt.Cells(lRow, 11).Value = .Range("E4")
      wsTgt.Cells(lRow, 12).Value = .Range("A9")
      wsTgt.Cells(lRow, 13).Value = .Range("B9")
      wsTgt.Cells(lRow, 14).Value = .Range("C9")
      wsTgt.Cells(lRow, 15).Value = .Range("C12")
      wsTgt.Cells(lRow, 16).Value = .Range("D21")
      wsTgt.Cells(lRow, 17).Value = .Range("C23")
      wsTgt.Cells(lRow, 18).Value = .Range("D32")
      wsTgt.Cells(lRow, 19).Value = .Range("G12")
      wsTgt.Cells(lRow, 20).Value = .Range("H21")
      wsTgt.Cells(lRow, 21).Value = .Range("G23")
      wsTgt.Cells(lRow, 22).Value = .Range("H32")
    End If
  End With
Next
End Sub

Note how there is no copying/pasting, sheet activation and continual recalculation of the output row.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel 2013 hyperlinks stop working	dharris	Excel	0	03-22-2015 05:34 AM
All Office products stop working	lethlean	Office	1	05-18-2013 07:07 AM
Save As cause app to stop working	rafterman	Word	1	03-04-2013 11:20 PM
GIF's stop working	Kwarior	PowerPoint	3	03-17-2010 09:59 PM
Select a range in one one workbook while working in other workbook	Slow&Steady	Excel	1	02-21-2010 03:34 AM