VBA Macros to combine all rows from sheets to a consolidated sheet if a criteria is met

[janboo]

Hello,
I am new to this site and grateful if someone can help write a macro. Slowing learning macro so grateful for any help!!!
I've googled and found ways of consolidating all sheets into one but I could not find one if criteria is met to copy all.

I have a workbook that can have as much as 115 sheets. The first 5 rows (A1 to A5) will always be consistent in each of the sheets. The rows would be:
A1: title
A2: Account
A3: description
A4: status
A5: summary
After that, it can have 5 rows of data or even over 100!

What I would like is a macro if status in A4 from the 115 sheets is not equal to OK, copy everything on the sheets to a new consolidated sheet, and named the tab "Consolidated".
I've attached a sample file and the result tab is how I hope the consolidated tab would look like.

Thanks again!

Janboo

 

[vletm]

janboo
Do You mean that if cell A4 text is as Status: ok?
As well as
after You run that macro more than once it would copy again ...?
How would You get those ~115 sheets to file which has that macro?
or
Do You have an idea to copy that macro somewhere?

 

[p45cal]

Unrefined:

Sub blah()
Dim CSht As Worksheet

With ActiveWorkbook
'check for existence of a consolidated worksheet and create one if necessary:
  On Error Resume Next
  Set CSht = .Sheets("Consolidated")
  On Error GoTo 0
  If CSht Is Nothing Then
    Set CSht = .Sheets.Add(After:=.Sheets(.Sheets.Count))
    CSht.Name = "Consolidated"
  End If
'run through all the worksheets:
  For Each sht In .Worksheets
    If sht.Name <> "Consolidated" Then 'excluding the Consolidated one, of course.
      If sht.Range("A4") <> "Status: ok" Then
        'Determine where stuff will be copied to:
        Set Destn = Sheets("Consolidated").UsedRange
        Set Destn = Destn.Offset(Destn.Rows.Count + 2).Cells(1)
        sht.UsedRange.Copy Destn 'Copy stuff across.
      End If
    End If
  Next sht
End With
End Sub

 

[janboo]

p45cal!!!! thank you so very much! it is EXACTLY what I needed!!!

So thankful for your help!

happy janboo!