Coping Data from multi sheets to a single sheet via criteria

[airbus319uk]

Hi All

I wonder if anyone can help me. I am using excel 2008 and I have a multi sheet workbook. I am looking at being able to copy data from all of the work sheets in the work book to a new worksheet in the work book. All the work sheets have the same headings. example
Work sheet 1 is called A, worksheet 2 is called B and worksheet 3 is called C and worksheet 4 is called D. In each worksheet the columns headings are Maker,Type,Qty,Date. I am wanting to copy all the row data from sheets A-C into sheet D depending on a criteria eg where column Date in sheets A-C are like *May09. I have been able to perform this function from 1 sheet eg copy from A to D using advanced filter..List range = 'A'!$A$1:$D$2000,Criteria range = $M$1:$M$2,Copy to = $A$1:$D$1. I tried changing the List range to be = 'A:C'!$A$1:$J$2000 hoping this would cover it but I get back an error. This is something I want to run often so if there is a better solution then please advise.

Thanks in advance

Mike

[Domski]

Hi,

Try this:

Please Login or Register  to view this content.

Hope it helps,

Dom

[airbus319uk]

Hi Dom

Tried that and worked a treat :-) thanks. I am now looking at putting the Criteria1:="=*MAY09" into sheet D as a cell so that you can type anything into the cell then run the macro instead of having to edit the macro each time before running. Any advise would be appriciated.

Thanks

Mike

[airbus319uk]

Hi Dom

Found an issue if the criteria doesnt exisit in one of the sheets, the ap fails and adds filter headers to the top of the sheet if failed on :-(

Thanks

Mike

[airbus319uk]

Can anyone still assist with this issue have I have still not found a way round my problem?
Thanks
Mike