Last row error

[ysherriff]

To all, I am getting a runtime erro: method of object _global failed when i try to consolidate my worksheets into a summary sheet. below is the code with the erro line in red. What can possible be wrong? I use this code all the time and all of a sudden won't work. Need help please. I ahve also attached the workbook. The module is called "Consolidate Sheets"

Sub ConsolidateSheets()
  Dim TargetSh As Worksheet
  Dim DestCell As Range
  Dim LastRow As Long
  Dim sh As Worksheet

  With Application
  .ScreenUpdating = False
  .Calculation = xlCalculationManual
  End With

' Delete the sheet "SUMMARY" if it exists
  Application.DisplayAlerts = False
  ' On Error Resume Next
  'ActiveWorkbook.Worksheets("SUMMARY").Delete
  'On Error GoTo 0
  Application.DisplayAlerts = True
 
 
 
  On Error Resume Next
  Set TargetSh = Worksheets("SUMMARY")
  On Error GoTo 0
  If TargetSh Is Nothing Then
  Set TargetSh = Worksheets.Add(before:=Sheets(1))
  TargetSh.Name = "SUMMARY"
  Else
  TargetSh.Cells.Clear
  End If
  Sheets("SUMMARY").Activate
  Range("a3:u309").ClearContents
 
  Set DestCell = TargetSh.Range("A1")
  Sheets("Template").Range("B6:V6").Copy DestCell 'copy header from template
  Set DestCell = DestCell.Offset(1, 0)
 
'  copy individual sheets to summary sheet
  For Each sh In ThisWorkbook.Sheets
  If sh.Name <> "Control" And sh.Name <> "Template" And sh.Name <> "Summary" And sh.Name <> "Insights" And sh.Name <> "AVP Analysis" And sh.Name <> "FSG Analysis" And sh.Name <> "12 Week Trend" And sh.Name <> "LivingCenter Analysis" Then
  LastRow = sh.Range("D400").End(xlUp).Row
  If LastRow > 1 Then
  sh.Range(Range("START_CELL").Address & ":" & sh.Range("V" & LastRow).Address).Copy 'Problem with this line
  TargetSh.Range(DestCell.Address).Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
  Set DestCell = DestCell.Offset(LastRow - 8)
 
  End If
  End If
 
  Next
 
  With Application
  .ScreenUpdating = True
  .Calculation = xlCalculationAutomatic
  End With
  ActiveSheet.UsedRange.EntireColumn.AutoFit 'AutoFit the column width
  Sheets("SUMMARY").Visible = False  'hide sheet
  Sheets("Insights").Activate
 
End Sub

 

[ysherriff]

i believe i found the error. did not set my sheets.

thanks