1018 sheets compiled into 1 master data dump

**ryandurick** · 06-27-2012, 02:19 PM

Afternoon Folks,

I have multiple excel worksheets that I would like to extract data from and dump into one master sheet.

The individual sheets follow the same template.

I would like to extract specific cells to the master sheet....I have seen a solution using a range but that will not work.

For Example - I need to capture the info located in cells - c1, h16, n32........and so on. Then take that info and put it neatly on a master sheet.

I am a complete novice....I need a ready made solution.

Can someone please help??

Thanks, Ryan

**tlafferty** · 06-27-2012, 02:26 PM

try this:

Sub CompileSheets()
Dim lngLastRow As Long
    For Each s In ThisWorkbook.Sheets
        If s.Name <> "Master" Then
                lngLastRow = Sheets("Master").Range("A1048576").End(xlUp).Row
            Sheets("Master").Range("A" & lngLastRow).Value = s.Range("C1").Value
            Sheets("Master").Range("B" & lngLastRow).Value = s.Range("H16").Value
            'enter your other ranges here
        End If
    Next s
End Sub

Press F11 on your keyboard. Click Insert Module and paste the above code. Then run the macro.

**ryandurick** · 06-28-2012, 01:27 PM

Thank you very much for helping. I have included a version of a similar sheet that works well but it exports a range of cells rather than the specific cells I need, and the format is messy. I am not sure if it would be easier to have the attached sheet extract cells c4, c5, h4, b10, b24, b33, b38, b51, b55, g58, I58, B59 and h6. This would ignore all the other fields that make it messy.

Ragarding your formula -
I have prepared the formula the best I know how but am not able to get it to run. When I debug it highlights the third line I get stuck. What should this line contain?? I do not understand what the program needs. If you could include a simplified deffinition for the line content, I should be able to piece it together.

Sorry I could not figure out how to properly package the code. Please see below.

Dim lngLastRow As Long

For Each s In ThisWorkbook.Sheets

If s.Name <> "Master" Then

lngLastRow = Sheets("Master").Range("A1048576").End(xlUp).Row

Sheets("Master").Range("A" & lngLastRow).Value = s.Range("C4").Value

Sheets("Master").Range("B" & lngLastRow).Value = s.Range("C5").Value

Sheets("Master").Range("B" & lngLastRow).Value = s.Range("H4").Value

Sheets("Master").Range("B" & lngLastRow).Value = s.Range("B10").Value

Sheets("Master").Range("B" & lngLastRow).Value = s.Range("B24").Value

Sheets("Master").Range("B" & lngLastRow).Value = s.Range("B33").Value

Sheets("Master").Range("B" & lngLastRow).Value = s.Range("B38").Value

Sheets("Master").Range("B" & lngLastRow).Value = s.Range("B51").Value

Sheets("Master").Range("B" & lngLastRow).Value = s.Range("B55").Value

Sheets("Master").Range("B" & lngLastRow).Value = s.Range("G58").Value

Sheets("Master").Range("B" & lngLastRow).Value = s.Range("I58").Value

Sheets("Master").Range("B" & lngLastRow).Value = s.Range("B59").Value

Sheets("Master").Range("B" & lngLastRow).Value = s.Range("H6").Value

End If

Next s

End Sub

**tlafferty** · 06-28-2012, 01:49 PM

I see the problem - I was under the assumption that all of your worksheets were in the same book and that you were going to cycle through each sheet, get the contents you're after, then write those contents to a "Master" sheet in the same workbook. From looking at your workbook, it seems like what you'd like to do is have code cycle through a list of workbooks, open each one, extract the needed data from each book, write to the "Master" sheet, then close the other workbooks. Is that what you'd like to do?

**ryandurick** · 06-28-2012, 03:36 PM

Yes, that sounds like what I am after.

Summary - I have about 1000 individual site specific audits (one workbook per site, each workbook has one sheet named "Lease Summary".) I have them all in the same folder, all use the same template and have no desire to open each file individually. I need to extract specific details from the individual Lease Summary page, and publish in single master tracker.

Goal - My customer has the site specific audit required, while I am able to view the project needs on a more global level.

Thank you again, once "I" figure this problem out.....I promise not to think any of this is "easy to figure out" and take a course. I am truly humbled.