Trying to automate data entry into one workbook from multiple workbooks

Hello all,

So I have hundreds of closed workbooks that describe one ID # (example: 10001, 10002, 10003). In each work book there are cells that are defined by name (example: Name 1, Name 2, Name 3) that I need to input into a master workbook. The workbooks I am pulling data from are not in a nice table. They're in a form. The Master Workbook will reorganize the data into a nice table format. What I have considered doing was having each cell pre-referenced to one of the blank forms and finding and replacing the file path to get the data. This becomes quite tedious. I figure this is a common task that people use everyday so I bring the question to you all. Do any of you have a good solutions to this?

I am open to using VBA.

Just ask me if you require more description of the problem.

Thanks!

The more information you have the easier this is going to be. eg, if you have a list of all the files you need to copy data from you can put them all (full path) into a column in Excel and loop down through them, opening, copying the data and closing again. If not then you'll have to mess around with filesystem objects to get a directory listing of the folder(s) they're in.

The good news is if the data is all in named ranges then it doesnt matter where it's stored in the workbook. You can access it using range("nameofrange").value

So for a group of source data files you know the location of:

Please Login or Register  to view this content.

thisworkbook.sheets("SheetName").range("A1048000").end(xlup).row + 1 selects the next empty cell in column A to copy the data to.