vba to copy from active sheet from email to active sheet from my files

[Taskmaster013]

Hi, In Excel 2007 I have a workbook sheet which I populate each day from an automated Excel data emailed file. I want to be able to copy the daily emailed sheet (sheet1) and paste it into my open workbook sheet on a macro button in my workbook sheet using vba code.

To help you understand, the current process is
- I open my excel workbook sheet (ready)
- Then I open my emailed excel data sheet
- I copy the full emailed data sheet and paste it into my workbook sheet (using view / switch to toggle from one to another).

I want to automate this process but the problem is that I never know what the emailed data sheet will be named although I know the name is 6 digits.xlsx if that helps?? My workbook sheet is named 'Cognos Input' (this is the destination sheet)

I never have more than the 2 excel books open at one time so I want my workbook to sense the emailed file and copy it to the 'Cognos Input' sheet on the click of a macro or even better automatically if this is possible.

P.S. I have searched for an answer for weeks to this!

Thanks, Mike