Populating Sheet from multiple files/tabs

[formypups]

I have a sheet template that calculates payroll information for a large group of employees. This template is populated and saved twice per day (one sheet for AM, one for PM) and one workbook is created per week (for a total of 14 sheets per week). I have workbooks created for every week of the year. Within this template is a small table (always in the same cells). I would like to import this table from each sheet (and from each workbook) to a single sheet that i can clean up and import into SPSS. Additionally, it would be nice if I could add columns showing the workbook file and sheet name from which came each row of data. The imported tables should be stacked one after another

I know for any given cell, I can use ='[Filename]SheetName'!Cell, but I haven't figured out how to automate this process for 30ish workbooks. Additionally, I can't figure out how to add the filename or sheetname as new columns and populate in the cells.

I really hope this makes sense. Happy to answer questions to help anyone understand what I'm asking for.

[JBeaucaire]

I would do this with a macro designed to open all the files stored in a specific folder and import them into a single new worksheet in the desired layout, organizing the data and adding the names as I go.

If you post a couple of sample template source files with fake data in it, then also upload a sample RESULTS workbook showing how the sample data from those two sample template workbooks would look after the import is complete, we can connect the dots for you.

Think this through now as you make your sample results, make sure it's exactly what you want and the sample template are truly representative of what must be imported.


Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic