MACRO: Gather from other workbook IF criteria met

[Legacy 171839]

Hi there,

I have a macro which opens up lots of workbooks, copies a specific range, then posts the info back into the 'master' workbook.

I would like to amend it so that when it opens the other workbooks, it will only copy the specified range, if the date in cell A2 = yesterdays date.

Have tried a few things and getting no where. Can anyone please help. Part of Macro am currently using as follows:

' GiftList_All_Gather Macro
    ChDir "W:\Shared-Data\Gift-List\GiftList Data Capture\Chris Campbell"
    Workbooks.Open Filename:= _
        "W:\Shared-Data\Gift-List\GiftList Data Capture\Chris Campbell\Chris Campbell.xls"
    Range("A2:K151").Select
    Selection.Copy
    Windows("GiftList Data Master.xlsm").Activate
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Windows("Chris Campbell.xls").Activate
    Application.DisplayAlerts = False
    ActiveWindow.Close
    Application.DisplayAlerts = False
    'done chris
    'start nicola
    Sheets("Nicola Taylor").Select
    ChDir "W:\Shared-Data\Gift-List\GiftList Data Capture\Nicola Taylor"
    Workbooks.Open Filename:= _
        "W:\Shared-Data\Gift-List\GiftList Data Capture\Nicola Taylor\Nicola Taylor.xls"
    Range("A2:K151").Select
    Selection.Copy
    Windows("GiftList Data Master.xlsm").Activate
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Windows("Nicola Taylor.xls").Activate
    ActiveWindow.Close
    Application.DisplayAlerts = False
    'done nicola
and so on...............

Thanks in advance...

 

[Curious ninja]

Hi Frierpie

I can't help but have tried to achieve something similar in the past (unsuccessfully).

I'm very interested to see any responses.