Macro - Master file to import data from another open file with variable file name

Hi everyone

Please bear with me, as I am new to macros.

I have created a spreadsheet (currently just a test/example) that I will send out via email to staff members.
They will input their information (eg. name, job title, phone number etc) and email it back (so there will be variable file names).
When I receive the file back from them, I will open that and run a macro from the master spreadsheet that I will copy the information into.

This is the macro I have so far... (this is just me testing, I haven't fully developed all the content, hence the simplicity)...

Please Login or Register  to view this content.

The problem is, when I receive another file and open it eg. "John.xls" (instead of "Jess.xls"), I try to run the macro, it doesn't work because it is a different file name.

Is there something I can replace in the line:
Windows("Jess.xls").Activate
that will allow me to use whatever the other file name is open (whether it be "Jess.xls", "John.xls", "Mary.xls", "Peter.xls" etc etc) and import the data from that file?
The "master.xls" is the file that contains the macro that will house all the data.

I have attached the example master file and two test files that people might send to me.

Master.xlsJess.xlsJohn.xls

Thanks in advance!

Jess.

Hi Nicky and Antonio

Thanks once again for your help!

I used both codes and once I figured out where they went they both worked perfectly.
I've developed the spreadsheets a bit more and I'll post them on here... I ended up creating a hidden spreadsheet where the data will go to so that the part employees fill out looks good and it is easier to copy the data across to the master.

Here is what the code looks like:

Please Login or Register  to view this content.

Thanks once again!

Cheers
Jess.

Defining Potential - Rob.xlsDefining Potential - Jess.xlsDefining Potential - Master Spreadsheet.xls