Copying column from 200+ workbooks to a master file (already attempted!)

[RZuban]

Hi,

I am new to VBA and looking to be able to automatically generate a master dataset by automatically copying a certain column from ~200 raw data workbooks. These will be accumulating over several weeks. For example, I may have 10 files in week 1, then 20, then 30, etc. I want to be able to drag and drop a new set of Excel data files into a folder with all existing ones, click a button, and have my master Excel updated with all data available to date.

I have attempted this with 3 workbooks and one master file. If it worked correctly, the data would appear in columns C-E in the master file. However, my code is not working. I am fairly certain at least one of the problems is the way I defined "ecolumn". I also know that my code may not work if any data file is alphabetically after "Master", so if anyone can suggest a fix to that as well, that would be helpful.

Additionally, I would also eventually like to transpose all of the data so that each data source is a row, if anyone also would be able to help with that.

I have pasted the module below, and attached the sample files. Could anyone help me with this? Thanks!

Please Login or Register  to view this content.

[nilem]

Hi RZuban,
try this (assumes that all the files are located in a single folder)

Please Login or Register  to view this content.

[RZuban]

Hi Nilem,

Thanks for your response. I've run into 2 problems.

1. The macro erases cell A1, so the column header in that cell gets deleted each time I run the code.
2. The macro does not recognize data which already pasted into the database. If I already have the first 10 lines of data in my master spreadsheet, I do not want this data to be pasted again as duplicates. This is important because I will not have all of my data at once, but rather gathering new data each day to add to existing data.

I am also having issues adapting my sample template to a larger spreadsheet, but if I can figure out issues #1 and #2 above, then I can focus on that issue.

It would also be very helpful if you could briefly explain what each line of code is doing. I am going to watch some VBA tutorials to try to understand the syntax better.

Thank you very much for your help!