Macro to open files in a folder, copy data if there is a match, close them

[Sgeng4]

Hi all,

I'm looking for some help making a macro.

I have a bunch of files in a folder. I want a macro that opens up each file one by one, and if the names match, then copy the value, date and certificate associated with that name onto the master spreadsheet.


So in the folder, I have excel files that have the following information in each sheet in the second tab:

Certificate	A8
date	Dec 9
name2	1.3
name3	1.5
name6	3.0

In the master sheet, I have the following columns:

UNIQUENAME	VALUE	DATE	CERTIFICATE
name1
name2
name3

I am looking for a way for excel to automatically fill in the value, date, and certificate numbers by looking them up in the sheets and pasting them into the correct row. For example, like so:

UNIQUENAME	VALUE	DATE	CERTIFICATE
name1
name2	1.3	Dec 9	A8
name3	1.5	Dec 9	A8

So far, I got the macro to open the file folder, and open the files one by one, but I don't know how to code it so it searches for matches, and copies the correct cells over.

Please Login or Register  to view this content.

Help is really appreciated!

Thank you. =)

[davesexcel]

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.


To add a file to a post

[Sgeng4]

Solved it!