Generate a pending list from multiple sheets

Hi there,

I'm really stuck with what I'm trying to do and I'm not even sure if it's possible.

In my line of work I send off samples to other labs for testing. These are logged onto a spreadsheet and the empty fields are filled once results are received. In my example spreadsheet, Column N is blank whenever a result has yet to be received and should therefore appear on the newly generated pending list sheet. I can get a list generated from one sheet but not multiple sheets.

If this can be done then great! But it would be ideal if I could take this one step further. The example spreadsheet given would be for 2014. Would it be possible to have the previous year's workbook (2013) scanned also for pending requests and added to the 'Pending' sheet list? This list should ideally be displayed in date order with the oldest pending request first.

Any help would be much appreciated,

Steven

Hi sktneer,

This works very well but I prefer having the pending sheet update automatically when opened rather than having to press a button.

Many thanks for your help though!

Steven

Originally Posted by JBeaucaire

The best way to grab data from multiple sheets in my opinion is a macro, VBA.

I've installed this macro into the PENDING sheet:

Option Explicit

Private Sub Worksheet_Activate()
Dim ws As Worksheet, LR As Long

Me.UsedRange.Offset(3).ClearContents            'clear current list
Application.ScreenUpdating = False              'speed up macro

For Each ws In Worksheets                       'check one sheet at a time
    If ws.Name <> Me.Name Then                  'make sure it's not the PENDING SHEET
        With ws
            .AutoFilterMode = False             'turn off prior filters
            .Rows(3).AutoFilter Field:=14, Criteria1:=""            'add a filter for blanks in column N
            LR = .Range("A" & .Rows.Count).End(xlUp).Row            'make sure there are some rows of data visible, then copy
            If LR > 3 Then .Range("A4:R" & LR).Copy Me.Range("A" & Rows.Count).End(xlUp).Offset(1)
            .AutoFilterMode = False             'remove the filter
        End With
    End If
Next ws

Application.ScreenUpdating = True               'update the screen
End Sub

This will apply an Autofilter to each sheeet in the workbook (Except PENDING) for blanks in column N and copy those rows to the PENDING sheet. THis is an EVENT macro, it occurs each time the PENDING sheet is brought up onscreen, so it will always show the most current data.

Hi J Beaucaire,

Thank you so much, this is exactly what I was hoping for!

Just one thing though. I may have other sheets within the workbook that don't require filtering. How would I modify the code to take this into consideration?

Regards,

Steven

Originally Posted by sktneer

Please find the attached sheet to see if this works as per your requirement.

Thanks again sktneer and yes, this now works perfectly.

This workbook may ultimately have other sheets that do not require filtering. So, for example, if I had another sheet named 'TOTALS' and this does not require filtering, how can this code be tweaked to take that into account?

Kind regards,

Steven

Originally Posted by JBeaucaire

For mine, if it's only a few sheets, one way is to exclude them by sheet names:

Option Explicit

Private Sub Worksheet_Activate()
Dim ws As Worksheet, LR As Long

Me.UsedRange.Offset(3).ClearContents            'clear current list
Application.ScreenUpdating = False              'speed up macro

For Each ws In Worksheets                       'check one sheet at a time
    If ws.Name <> Me.Name And ws.Name <> "TOTALS" Then                  'make sure it's not the PENDING SHEET
        With ws
            .AutoFilterMode = False             'turn off prior filters
            .Rows(3).AutoFilter Field:=14, Criteria1:=""            'add a filter for blanks in column N
            LR = .Range("A" & .Rows.Count).End(xlUp).Row            'make sure there are some rows of data visible, then copy
            If LR > 3 Then .Range("A4:R" & LR).Copy Me.Range("A" & Rows.Count).End(xlUp).Offset(1)
            .AutoFilterMode = False             'remove the filter
        End With
    End If
Next ws

Application.ScreenUpdating = True               'update the screen
End Sub

You can add more of those exclusions is you wish.


To access the code in the workbook, right-click on the PENDING tab name and select VIEW CODE.

It would never be more than a few sheet names so this is ideal for my purposes.

Many thanks for your help. This will save me a great deal of time at work!

Take care,

Steven

Originally Posted by sktneer

You have to include the code in red color to achieve that.....

Private Sub Worksheet_Activate()
Dim ws As Worksheet
Dim rng As Range, cell As Range
Dim lr As Long, lr1 As Long
Application.ScreenUpdating = False
lr1 = Sheets("Pending").Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Pending").Range("A4:A" & lr1).EntireRow.Clear
For Each ws In Worksheets
    If ws.Name <> "Pending" And ws.Name <> "Totals" Then
        lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
        Set rng = ws.Range("A4:A" & lr)
        For Each cell In rng
            If cell.Offset(0, 13).Value = "" Then
                lr1 = Sheets("Pending").Cells(Rows.Count, 1).End(xlUp).Row + 1
                ws.Range("A" & cell.Row & ":R" & cell.Row).Copy Sheets("Pending").Range("A" & lr1)
            End If
        Next cell
    End If
Next ws
Sheets("Pending").Activate
Application.ScreenUpdating = True
End Sub

Yes, I see that. You've been a great help.

Thanks for your time,

Steven