Microsoft Office Forums - View Single Post - [Solved] Workbook Stop Working When Run VBA

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