VBA - Creating Consolidated Report

[LNT123]

Hello,

I need help with the VBA coding for one of my spreadsheets. I have a workbook that has many different sheets of information, all populating onto a more consolidated report (calling it 'SITREP') using formulas. From 'SITREP' I want to create a clean report ('CLEAN'). If there is nothing to report in the various sections of the 'SITREP' worksheet, the 'CLEAN' sheet is supposed to get rid of unnecessary lines to showcase a more consolidated report that is easier to read.

The problem is that when I press the 'CLEAN' button to create a 'CLEAN' worksheet, one of the sections does not clean, but instead shows only one line of formulas from the 'SITREP' sheet. For example, if there are three lines of information in the 'SITREP' sheet, then all three lines should appear on the 'CLEAN' worksheet. If there are no lines of information on the 'SITREP' sheet, then those unnecessary lines should delete. The section that I'm having trouble with is Section9D.

'***Process 9. Venues & Villages***
ActiveSheet.Range("Section9").Select
ActiveCell.Offset(1, 0).Select

Do Until ActiveCell.Row = ActiveSheet.Range("Section9A").Row
 If ActiveCell.Value = "" Then
   ActiveCell.EntireRow.Delete
 Else
  ActiveCell.Offset(1, 0).Select
 End If
Loop

ActiveSheet.Range("Section9A").Select
ActiveCell.Offset(1, 0).Select

Do Until ActiveCell.Row = ActiveSheet.Range("Section9B").Row
 If ActiveCell.Value = "" Then
   ActiveCell.EntireRow.Delete
 ElseIf ActiveCell.Offset(0, 1).Value = 0 Then
   ActiveCell.EntireRow.Delete
 Else
  Sheets("Edit").Range("A11:E11").Copy
  Sheets("CLEAN SITREP").Select
  Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
  MyRow = ActiveCell.Row
  Rows(MyRow).RowHeight = 15
  ActiveCell.Offset(1, 0).Select
 End If
Loop

If ActiveSheet.Range("Section9B").Row - ActiveSheet.Range("Section9A").Row = 1 Then
 Sheets("Edit").Rows("4:4").Copy
 ActiveCell.Insert Shift:=xlDown
 ActiveCell.Offset(1, 0).Select
End If

'Add blank rows between sections
Sheets("Edit").Rows("5:5").Copy
ActiveCell.Insert Shift:=xlDown

ActiveSheet.Range("Section9B").Select
ActiveCell.Offset(1, 0).Select

Do Until ActiveCell.Row = ActiveSheet.Range("Section9C").Row
 If ActiveCell.Value = "" Then
   ActiveCell.EntireRow.Delete
 ElseIf ActiveCell.Offset(0, 1).Value = 0 Then
   ActiveCell.EntireRow.Delete
 Else
  Sheets("Edit").Range("A11:E11").Copy
  Sheets("CLEAN SITREP").Select
  Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
  MyRow = ActiveCell.Row
  Rows(MyRow).RowHeight = 15
  ActiveCell.Offset(1, 0).Select
 End If
Loop

If ActiveSheet.Range("Section9C").Row - ActiveSheet.Range("Section9B").Row = 1 Then
 Sheets("Edit").Rows("4:4").Copy
 ActiveCell.Insert Shift:=xlDown
 ActiveCell.Offset(1, 0).Select
End If

'Add blank rows between sections
Sheets("Edit").Rows("5:5").Copy
ActiveCell.Insert Shift:=xlDown

ActiveSheet.Range("Section9C").Select
ActiveCell.Offset(1, 0).Select


ActiveSheet.Range("Section9C").Select
ActiveCell.Offset(1, 0).Select

Do Until ActiveCell.Row = ActiveSheet.Range("Section9D").Row
 If ActiveCell.Value = "" Then
   ActiveCell.EntireRow.Delete
 ElseIf ActiveCell.Offset(0, 1).Value = "Sched: 0;     Actual: 0;     Lates/Earlies: 0" Then
   ActiveCell.EntireRow.Delete
 Else
'  Sheets("Edit").Range("A11:E11").Copy
'  Sheets("CLEAN SITREP").Select
'  Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
'        SkipBlanks:=False, Transpose:=False
'  MyRow = ActiveCell.Row
'  Rows(MyRow).RowHeight = 15
  ActiveCell.Offset(1, 0).Select
 End If
Loop

If ActiveSheet.Range("Section9D").Row - ActiveSheet.Range("Section9C").Row = 1 Then
 Sheets("Edit").Rows("4:4").Copy
 ActiveCell.Insert Shift:=xlDown
 ActiveCell.Offset(1, 0).Select
End If

'Add blank rows between sections
Sheets("Edit").Rows("5:5").Copy
ActiveCell.Insert Shift:=xlDown

ActiveSheet.Range("Section9D").Select
ActiveCell.Offset(1, 0).Select


Do Until ActiveCell.Row = ActiveSheet.Range("Section9E").Row
 If ActiveCell.Value = "" Then
   ActiveCell.EntireRow.Delete
 ElseIf ActiveCell.Offset(0, 1).Value = "Crew Rostered: ; FLT/TH Rostered: ; Supervisor Rostered:" Then
   ActiveCell.EntireRow.Delete
 Else
  Sheets("Edit").Range("A11:E11").Copy
  Sheets("CLEAN SITREP").Select
  Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
  MyRow = ActiveCell.Row
  Rows(MyRow).RowHeight = 15
  ActiveCell.Offset(1, 0).Select
 End If
Loop

If ActiveSheet.Range("Section9E").Row - ActiveSheet.Range("Section9D").Row = 1 Then
 Sheets("Edit").Rows("4:4").Copy
 ActiveCell.Insert Shift:=xlDown
 ActiveCell.Offset(1, 0).Select
End If

'Add blank rows between sections
Sheets("Edit").Rows("5:5").Copy
ActiveCell.Insert Shift:=xlDown

ActiveSheet.Range("Section9E").Select
ActiveCell.Offset(1, 0).Select

The 'EDIT' sections refer to another sheet in the workbook that generate consolidated references for the 'CLEAN' report. In other words, if there is nothing to report, then 'EDIT' A4:E4 populate with the words 'Nothing to Report' and a space is added below that phrase which references 'EDIT' A5:E5. EDIT A9:E9 also says 'nothing to report' as does EDIT A1:E1.

I hope that I've explained the issue effectively, please let me know if you are able to assist!