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.
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.