Draw Data from multiple sheets:

Hi

I am trying to draw data from multiple sheets.

I am using some code I got from ozgrid.com to draw my own invoices to one sheet:

function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _
                          Col_num As Integer, Optional Range_look As Boolean)

Dim wSheet As Worksheet

Dim vFound



On Error Resume Next



    For Each wSheet In ActiveWorkbook.Worksheets
        With wSheet
        Set Tble_Array = .Range(Tble_Array.Address)
            vFound = WorksheetFunction.VLookup _
            (Look_Value, Tble_Array, _
            Col_num, Range_look)
        End With

        If Not IsEmpty(vFound) Then Exit For

    Next wSheet



    Set Tble_Array = Nothing

    VLOOKAllSheets = vFound

End Function

That works brilliantly IF you have the invoice number, but where I am putting in supplier invoices and the numbers are different.


We have 1 sheet for every day of the month.
How can I return the values of the cell simply by the condition that they are populated? I have about 10 rows allocated for everyday, but usually we only use 2 or 3, once a week or so we use all 10. I could use absolute reverence, but that would mean up to 8 blank rows for every day.

Is there any way for it just to return the cells, in order, from all the sheets skipping the blank ones?

Thanks
Mark