Need Macro to compile data from hundreds of worksheets

[JimBobCooter]

Hello, I'm new to writing macros and have not been able to make much headway using online instructions or youtube. I have a few hundred excel files, each containing 3 worksheets. I'd like to pull data from a specific worksheet called "Physician Results" and have the data compiled into a single new table. Note, the data that I would like to compile are all the results of formulas calculated, but I just need the values (I would normally copy and paste special -> values for these). The original worksheets contain the data I would like to compile as follows:

In cells O29:P38

1	0
2	20
3	40
4	60
5	80
6	100
7	120
8	140
9	160
10	180

<tbody>
</tbody>

In cells R29:V38

7	2	9	4.5	Yes
16	4	20	4.1	Yes
8	3	11	4.1	Yes
10	3	13	4.5	Yes
15	4	19	4.4	Yes
32	5	37	4.1	Yes
27	4	31	4.1	Yes
29	5	34	4.3	Yes
28	5	33	4.4	Yes
27	6	33	3.8	Yes

<tbody>
</tbody>

In cell F13,

Doe, Jane D

<tbody>
</tbody>

In cell G30,

25

<tbody>
</tbody>

In cell G32,

3

<tbody>
</tbody>

In cell G34,

28

<tbody>
</tbody>

I would like the data compiled into a summary worksheet as follows:

1	0	7	2	9	4.5	Yes	Doe, Jane D	25	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28
2	20	16	4	20	4.1	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes	Doe, Jane D	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28
3	40	8	3	11	4.1	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes	Doe, Jane D	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28
4	60	10	3	13	4.5	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes	Doe, Jane D	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28
5	80	15	4	19	4.4	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes	Doe, Jane D	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28
6	100	32	5	37	4.1	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes	Doe, Jane D	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28
7	120	27	4	31	4.1	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes	Doe, Jane D	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28
8	140	29	5	34	4.3	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes	Doe, Jane D	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28
9	160	28	5	33	4.4	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes	Doe, Jane D	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28
10	180	27	6	33	3.8	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes	Doe, Jane D	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3	<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28

<tbody>
</tbody>

The data from each original worksheet would appear in the row after the data from the first original worksheet so that the result is one summary table with ~2000 rows and 11 columns.

Any help here would be hugely appreciated.
Thanks for your consideration.

 

[JLGWhiz]

Are the files all in one directory? If so, what is the directory path? If not, is there a list of the file names (with file extension) and their respective paths on a sheet that can be referenced? One of these options must be available in order to automate the opening and closing of the files to do the copying. Otherwise, the code would have to be written so the user would manually open and close all the files.

 

[tonyyy]

JimBobCooter,

You might consider the following...

Sub AnotherMaster_1021484()
Application.ScreenUpdating = False
Dim wb As Workbook, wb2 As Workbook
Dim FolderName As String, fileName As String
Dim errLog As String, err As Boolean
Dim arr1() As Variant, arr2() As Variant
Dim c As Long, r As Long, n As Long, kount As Long
Dim rng As Range
Dim value1 As String, Value2 As Long, value3 As Long, value4 As Long

''''Select folder that contains files
With Application.FileDialog(msoFileDialogFolderPicker)
  .AllowMultiSelect = False
  If .Show = 0 Then Exit Sub
  FolderName = .SelectedItems(1) & "\"
End With

Set wb = Workbooks.Add
fileName = Dir(FolderName & "*.xls?")
err = False
kount = 0
n = 10

''''Loop through files
Do While fileName <> ""
    Set wb2 = Workbooks.Open(FolderName & fileName)
        On Error GoTo errHandler
        Set rng = wb2.Sheets("Physician Results").Range("O29:V38")
        value1 = wb2.Sheets("Physician Results").Range("F13")
        Value2 = wb2.Sheets("Physician Results").Range("G30")
        value3 = wb2.Sheets("Physician Results").Range("G32")
        value4 = wb2.Sheets("Physician Results").Range("G34")
        
        ''''Populate arrays
        arr1 = Application.Transpose(rng)
        ReDim Preserve arr2(1 To 12, 1 To n)
        For r = 1 To 8
            For c = 1 To 10
                arr2(r, c + kount) = arr1(r, c)
            Next c
        Next r
        For c = 1 To 10
            arr2(9, c + kount) = value1
            arr2(10, c + kount) = Value2
            arr2(11, c + kount) = value3
            arr2(12, c + kount) = value4
        Next c
        n = n + 10
        kount = kount + 10
Nexxt:
    wb2.Close savechanges:=False
    fileName = Dir
Loop

''''Write array to new workbook
With wb.Sheets(1)
    .Range(Cells(1, 1), Cells(n - 10, 12)) = Application.Transpose(arr2)
    .Columns(3).Delete
    .Columns.AutoFit
End With

Application.ScreenUpdating = True
MsgBox "The dishes are done, dude!"
If err = True Then MsgBox "These files do not have a Physician Results worksheet:" & vbCrLf & vbCrLf & errLog
Exit Sub
errHandler:
    errLog = errLog & wb2.Name & vbCrLf
    err = True
    Resume Nexxt
End Sub

Cheers,

tonyyy

 

[JimBobCooter]

Thanks, JLGWhiz, yes, the files will all be in one directory. The directory path is: /Users/JameelShah 1/Documents/Center for SIBO Testing/CFST Laboratory/BreathTracker Results.

Thanks for your help here.

 

[JimBobCooter]

Thanks, Tonyyy! I'll give this a shot.