Expiry dates from multiple sheets to populate into a final list.

[KyleJackMorrison]

Hello,

I have a document which has separate sheets for the 10 different containers at work. In these containers are 40 items which all have different expiry dates which are stored in Cells D2-D40.
I would like to have a summary sheet which would search all the expiry dates across the 10 sheets and populate the expired items into one final list.
This is so it is easier to order the new items instead of going through each sheet manually.
I have a code already which will alert the user of what items are expired in a MsgBox however i don't know how copy them to a seperate sheet and not override what's already there.

Any help is much appreciated.

KJM

 

[cooper645]

Hi,

I have done a bit of work on some code as follows.
it assumes that you have a Sheet named Summary, and that your 10 container sheets are the sheets 1 through 10.

[COLOR=#0000ff]Option Explicit[/COLOR]

[COLOR=#0000ff]Sub[/COLOR] Summary()


[COLOR=#0000ff]Dim[/COLOR] c [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]Range[/COLOR], LastRow [COLOR=#0000ff]As Long[/COLOR], i [COLOR=#0000ff]As Integer[/COLOR]


Application.ScreenUpdating = [COLOR=#0000ff]False[/COLOR]


Sheets("Summary").Cells.Clear


[COLOR=#0000ff]For[/COLOR] i = 1 [COLOR=#0000ff]To[/COLOR] 10
  Sheets(i).Select
    [COLOR=#0000ff]For[/COLOR] [COLOR=#0000ff]Each[/COLOR] c [COLOR=#0000ff]In[/COLOR] Range("D2:D40")
        [COLOR=#0000ff]If[/COLOR] c.Value < Date [COLOR=#0000ff]Then[/COLOR]
                LastRow = Sheets("Summary").Cells(Rows.Count, "D").End(xlUp).Row
                c.EntireRow.Copy Worksheets("Summary").Range("A" & LastRow + 1)
                LastRow = LastRow + 1
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]        Next[/COLOR] c
[COLOR=#0000ff]Next[/COLOR] i


Sheets("Summary").Select


Application.ScreenUpdating = [COLOR=#0000ff]True[/COLOR]


MsgBox "Summary Sheet Updated"


[COLOR=#0000ff]End Sub[/COLOR]

If this is not the case, just let me know what your container sheets are called and we can pop them in an array.

Coops