Workbook Stop Working When Run VBA

macropod · #2 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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

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