Excel/roll up many sheets to a master sheet
I have someone at my company creating file audits. She starts with a clean spreadsheet (just fields to fill out but no data yet) and she fills out the form. Each time, she saves it with a new name and keeps the original form blank. All of these individual spreadsheets are kept in the same folder. I need a master sheet, that is separate from the others, to roll up all of the data. How do I do this?
ANSWER: That depends how the data is laid out on the individual sheets I guess. Can you try to explain that or send a screenshot?
---------- FOLLOW-UP ----------
QUESTION: I can send the sheets to you but I don't have your email. When I try to paste, it becomes a difficult to read mess
The sheets are each broken up into nine sections. Each section has 4 to 12 questions. The answers are Yes, No, NA, and #days. The auditor puts a number into each field and saves the file with a new name. I may have anywhere from 20 to 100s of these sheets. Hope this helps
Here is what you could do:
- Open one of the form files
- Open your reporting workbook
- On a new worksheet, one single row, create formulas pointing directly to the cells where data has been entered into (one cell per cell, just type the equal sign, switch to the other workbook and click on the answer cell in question)
- Name that sheet "Data" (without quotes)
- Have a worksheet called "AllData" (without quotes)
Now try this macro (not tested):
Dim vFiles As Variant
Dim lCount As Long
Dim vFilename As Variant
Dim sPath As String
sPath = "c:\windows\temp\"
vFilename = Application.GetOpenFilename("Microsoft Excel files (*.xls),*.xls", , "Please select the file(s) to import", , True)
If TypeName(vFilename) = "Boolean" Then Exit Sub
For lCount = LBound(vFilename) To UBound(vFilename)
ThisWorkbook.ChangeLink ThisWorkbook.LinkSources(xlExcelLinks)(1), vFilename(lCount)
.Range("A", .Rows.Count).Offset(1).PasteSpecial xlValues