Need Macro to compile data from hundreds of worksheets

J

JimBobCooter

New Member
Joined
Jan 27, 2017
Messages
6
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
[TABLE="width: 100"]
<tbody>[TR]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]180[/TD]
[/TR]
</tbody>[/TABLE]

In cells R29:V38
[TABLE="width: 300"]
<tbody>[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]9[/TD]
[TD]4.5[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]4[/TD]
[TD]20[/TD]
[TD]4.1[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]3[/TD]
[TD]11[/TD]
[TD]4.1[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[TD]13[/TD]
[TD]4.5[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]4[/TD]
[TD]19[/TD]
[TD]4.4[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]5[/TD]
[TD]37[/TD]
[TD]4.1[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]4[/TD]
[TD]31[/TD]
[TD]4.1[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]5[/TD]
[TD]34[/TD]
[TD]4.3[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]5[/TD]
[TD]33[/TD]
[TD]4.4[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]6[/TD]
[TD]33[/TD]
[TD]3.8[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

In cell F13,
[TABLE="width: 100"]
<tbody>[TR]
[TD]Doe, Jane D[/TD]
[/TR]
</tbody>[/TABLE]

In cell G30,
[TABLE="width: 100"]
<tbody>[TR]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

In cell G32,
[TABLE="width: 100"]
<tbody>[TR]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

In cell G34,
[TABLE="width: 100"]
<tbody>[TR]
[TD]28[/TD]
[/TR]
</tbody>[/TABLE]

I would like the data compiled into a summary worksheet as follows:
[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD]2[/TD]
[TD]9[/TD]
[TD]4.5[/TD]
[TD]Yes[/TD]
[TD]Doe, Jane D[/TD]
[TD]25[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20[/TD]
[TD]16[/TD]
[TD]4[/TD]
[TD]20[/TD]
[TD]4.1[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes[/TD]
[TD]Doe, Jane D[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]40[/TD]
[TD]8[/TD]
[TD]3[/TD]
[TD]11[/TD]
[TD]4.1[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes[/TD]
[TD]Doe, Jane D[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]60[/TD]
[TD]10[/TD]
[TD]3[/TD]
[TD]13[/TD]
[TD]4.5[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes[/TD]
[TD]Doe, Jane D[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]80[/TD]
[TD]15[/TD]
[TD]4[/TD]
[TD]19[/TD]
[TD]4.4[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes[/TD]
[TD]Doe, Jane D[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]100[/TD]
[TD]32[/TD]
[TD]5[/TD]
[TD]37[/TD]
[TD]4.1[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes[/TD]
[TD]Doe, Jane D[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]120[/TD]
[TD]27[/TD]
[TD]4[/TD]
[TD]31[/TD]
[TD]4.1[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes[/TD]
[TD]Doe, Jane D[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]140[/TD]
[TD]29[/TD]
[TD]5[/TD]
[TD]34[/TD]
[TD]4.3[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes[/TD]
[TD]Doe, Jane D[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]160[/TD]
[TD]28[/TD]
[TD]5[/TD]
[TD]33[/TD]
[TD]4.4[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes[/TD]
[TD]Doe, Jane D[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]180[/TD]
[TD]27[/TD]
[TD]6[/TD]
[TD]33[/TD]
[TD]3.8[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>Yes[/TD]
[TD]Doe, Jane D[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>25[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>3[/TD]
[TD]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}</style>28[/TD]
[/TR]
</tbody>[/TABLE]

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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
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.
 
Upvote 0
JimBobCooter,

You might consider the following...

Code: 
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
M
  • Question Question
Conditional Formatting - change cell colour and and adding text
Replies
5
Views
4K
Fluff
Fluff
M
  • Question Question
How to highlight a cell only if value exist and leave if blank
Replies
5
Views
800
mattbarb
M
K
  • Question Question
IF(ISNA...) with VLOOKUP on another worksheet to return "M" if not present on other worksheet?
Replies
4
Views
1K
Peter_SSs
Peter_SSs
M
  • Question Question
How can I project sheet formulas whist having a save button on other cells to project them?
Replies
1
Views
707
Yongle
Yongle

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top