You are here:

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):

Sub ImportLotsOfFiles()
   Dim vFiles As Variant
   Dim lCount As Long
   Dim vFilename As Variant
   Dim sPath As String
   sPath = "c:\windows\temp\"
   ChDrive sPath
   ChDir sPath
   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)
       With ThisWorkbook.Worksheets("AllData")
         .Range("A", .Rows.Count).Offset(1).PasteSpecial xlValues
       End With
End Sub

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jan Karel Pieterse


Excel and Excel/VBA questions


Excel MVP

Self employed Excel developer

Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2017 All rights reserved.