Consolidate 7 worksheet into 1 worksheet if "Application no = Blank"

Cyee,

To identify the rows where application no is blank, which other rows will always have data in them? I checked some of your tabs and sometimes Country is filled up and Application no is blank, otherwise BU is filled up and Application no is blank. Should i consider Country to be the most filled up column? The reason i ask this is i would like to identify which is the last row in each sheet that i need to check for.

Therefore, could you dont set for the last row to check?

What do you mean by this?

I wanted to know if there is a way to identify the last row in which you have data entered in your file. Its not static and you have color formats so i am not able to identify using code.

You can use this code. Ensure that the BU column is filled with information for all rows which need to be included in the Consolidated sheet.

Option Explicit
Dim lrow As Long
Dim i As Long
Dim j As Long

Sub compile_data()

Application.ScreenUpdating = False

If Not Evaluate("ISREF(Summary!A1)") Then
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Summary"
End If
Worksheets("Food").Rows("7:7").Copy Worksheets("Summary").Range("A1")

For i = 1 To Worksheets.Count
    With Worksheets(i)
        If .Name <> "Summary" And .Name <> "COVER" Then
            lrow = .Range("A" & Rows.Count).End(xlUp).Row
            For j = 8 To lrow
                If .Range("H" & j).Value = "" Then
                    .Range("A" & j & ":K" & j).Copy Worksheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                End If
            Next j
        End If
    End With
Next i

Application.ScreenUpdating = True

End Sub

option, press button "Non-Application report" on Cover Sheet