Struggling to move & link data without access

You could use Data|Advanced Filter to copy data to another sheet. Automating it isn't difficult...

Sub AdvFilter()
    'Assuming a data table in Sheet1 and criteria and extract ranges in Sheet2
    With Worksheets("Sheet2")
    Worksheets("Sheet1").Range("DataTable").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=.Range("Criteria"), _
        CopyToRange:=.Range("Extract")
        End With
End Sub

Hi that looks good but I'm struggling on the criteria, if column D is the column that potential will have the Y in it, how do you write that as the criteria.


Thanks again I can think of many other things I should have used advanced filter for

It ok Ive worked that one out
It works but I dont think I can use it as the blank columns in the new filtered sheet would be filled in, as the data in the master data sheet is updated daily when the filter is re-run that data will be lost as it will bring back the blanks from the original. Is there any way to filter & link the cells automatically so the new filtered data when information is input in the blank cells updates the same blank cells in the master data. Sorry if that doesnt make sense.
It all needs to be automated as it will be run by guys with less excel knowledge than me.

Basically in access I would have appended the new data into a table with blank columns each day then put a query to say show those that have Y in that column but have blank columns at the end, have a form to update those columns then when the query is refreshed they will no longer be blank so will not show & the master data will be fully updated.
Its quite difficult to put this in excel terms as its not a really a database but I have to use it as one.

See the attached file...