Macro to copy file names and data from each file in a folder into master spreadsheet

Hi everyone,

I'm new to this forum as I only found it a week or so ago when looking for some help with a macro problem. I found some great help with my previous problem so I was hoping someone could help me with another issue I'm having. I've had a look at old threads and have found some code but I can't seem to edit and combine it correctly to fit exactly what I want.

Basically I have a number of excel files in a folder. I want to create a macro that will copy the name of each file (preferably without the .xls but that's not hugely important) and some data from each into a master spreadsheet. Each file has three columns of data - A, B and C. I only want to copy column C for each (this column does contain some blank cells which I don't want copied).

So in my master spreadsheet I want column A to contain the name of the first file in the folder and the data from column C. Column B should contain the name of the second file in the folder and the data from column C. And so on.

So far I've found two pieces of code - one that lists the file names and one that copies the data but they aren't working how I would like and I need to combine them into one macro if possible.

The first:

Please Login or Register  to view this content.

The problem with this is that it pastes the file names vertically in column A starting from cell 2. I need the names to go across row 1 starting from column A (like a header row).


The second:

Please Login or Register  to view this content.

The problem with this is that it copies the data in columns A, B and C from each file when I only want column C. Also it pastes the data vertically whereas I require it to be horizontally.
In addition, the data does not seem to have been pasted directly. Instead of having the data form the first file, then the data from the next file etc it seems to have grouped together data which is the same across multiple files. So for example each file might contain a cell in a column A with the text "ff_add_paragraph". After pasting the data into the master file the first 100 or so cells will be "ff_add_paragraph" because its copied that value from 100 files and pasted them all in a row instead of pasting everything directly. I also noticed that it doesn't seem to have pasted everything from each file as when I checked one of the files there were some values which did not show up at all in the master file.

I hope I've explained this properly. As you can probably tell I have absolutely no experience with Macros or VBA so I would really appreciate it anyone could help me out! If you need any other information just let me know!

Thanks!

Do you have a sample file that you can upload?

Try this code

Please Login or Register  to view this content.

It clears the contents of the masterfile each time you run it. So only fresh data is included in the master file. If you do not want this to happen, remove this line of code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

Try this...change that line from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Change this line from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

The code works in this way -

It copies the filename in row 1. Then copies the data from the source file as is. Then it sorts column 1 so that the blanks are removed. Then it moves to column 2.

So the blanks shouldnt be there.

Are those really blanks or is there some formula in them?

We can do this.

Once the data is copied, we can run a small bit of code to remove the blank rows.