Data Pull from Multiple Worksheets based on Criteria!!!

Maybe:

Sub RajeshkumarR()
Dim i As Long, x As String, y As Range, z As Long, ii As Long
x = CDate(Sheets("Consolidation").Range("G1").Value)
For i = 4 To 9
Sheets(i).Activate
For ii = 1 To Sheets(i).UsedRange.Columns.count
    If Cells(1, ii) Like "*" & x & "*" Then
        z = Cells(Rows.count, ii).End(3).row
        On Error Resume Next
        Sheets(i).Range(Cells(1, ii), Cells(z, ii)).AutoFilter 1, "<0"
        Sheets(i).Range(Cells(2, "A"), Cells(z, "A")).Resize(, 2).SpecialCells(12).Copy Sheets("Consolidation").Range("A" & Rows.count).End(3)(2)
        Sheets(i).Range(Cells(2, ii), Cells(z, ii)).SpecialCells(12).Copy Sheets("Consolidation").Range("C" & Rows.count).End(3)(2)
        With Sheets("Consolidation")
            .Range(.Cells(.Range("D" & Rows.count).End(3)(2).row, "D"), .Cells(.Range("C" & Rows.count).End(3).row, "D")) = Sheets(i).Name
        End With
        Sheets(i).AutoFilterMode = False
        On Error GoTo 0
        Exit For
        GoTo zz
    End If
Next ii
zz:
Next i
ActiveWorkbook.Save True
End Sub

Dear JOHN H. DAVIS,

Thank You So Much for your suggestions, It is working Amazingly!

Thanks & Regards,
Rajeshkumar R

You're welcome. Glad to help out and thanks for the feedback.