Getting data from multiple books and sheets with multiple conditions

B

bwells

New Member
Joined
Sep 10, 2014
Messages
1
I am creating a master database for work which gets specific data from a folder with multiple workbooks with multiple worksheets. Each workbook has the same form template on each worksheet used. However, a workbook could have 1 to 30 worksheets. The below code is what I have now with help from a past forum post. The end result is to look like Table 2. The code does everything I need except for two things.

1 - Need a loop to go through every worksheet in every workbook so code doesn't have to be copied for 1 to 30 worksheets.

2 - Conditional statement that states if a joint is accepted or rejected. If the joint is rejected then for what reason. There are 18 different reasons a joint is rejected, see Table 1, but for now lets say 1 to 18. One joint has a A, B, and C. If A, B, or C is marked as 1 of the 18 conditions then joint is rejected for that condition. If A, B, or C is not marked as 1 of the 18 condition then the joint is accepted.

Any help with this would be greatly appreciated.

Table 1
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1239"]
<tbody>[TR]
[TD="colspan: 2"]Project[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 2"]WPS NO[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 2"]Date[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 3"]Machine Number[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]OD[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]WT[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"]Fitting, Seam, Or Joint Number[/TD]
[TD="colspan: 3"]Film Interval Number[/TD]
[TD="colspan: 4"] [/TD]
[TD]SLAG [/TD]
[TD]Porosity [/TD]
[TD]Porosity with Tail[/TD]
[TD] Crack [/TD]
[TD] Lack of Penetration [/TD]
[TD]Lack of Fusion[/TD]
[TD] Internal Covexity[/TD]
[TD]Internal Concavity[/TD]
[TD]Tungsten[/TD]
[TD]Melt-Through[/TD]
[TD] Burn- Through[/TD]
[TD] Crater Pit[/TD]
[TD]Oxidation[/TD]
[TD] Internal Undercut[/TD]
[TD]External Undercut[/TD]
[TD] Aligned Indications[/TD]
[TD]Weld Contour[/TD]
[TD]Mis-Match[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 5"] [/TD]
[TD]Accept[/TD]
[TD]Reject[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[TD="colspan: 5"] [/TD]
[TD="colspan: 6"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 5"] [/TD]
[TD="colspan: 6, align: left"]
clip_image001.png

<tbody>
[TD="colspan: 6"] [/TD]

</tbody>
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]1[/TD]
[TD="colspan: 3"]A[/TD]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 5"]WID[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 6"] [/TD]
[/TR]
[TR]
[TD="colspan: 2"] [/TD]
[TD="colspan: 3"]B[/TD]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 5"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 6"]
clip_image001.png
[/TD]
[/TR]
[TR]
[TD="colspan: 2"] [/TD]
[TD="colspan: 3"]C[/TD]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 5"] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"]2[/TD]
[TD="colspan: 3"]A[/TD]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 5"]WID[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"] [/TD]
[TD="colspan: 3"]B[/TD]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 5"] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"] [/TD]
[TD="colspan: 3"]C[/TD]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 5"] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"]3[/TD]
[TD="colspan: 3"]A[/TD]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 5"]WID[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"] [/TD]
[TD="colspan: 3"]B[/TD]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 5"] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"] [/TD]
[TD="colspan: 3"]C[/TD]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 5"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 6"] [/TD]
[/TR]
[TR]
[TD="colspan: 2"]4[/TD]
[TD="colspan: 3"]A[/TD]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 5"]WID[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 6"] [/TD]
[/TR]
[TR]
[TD="colspan: 2"] [/TD]
[TD="colspan: 3"]B[/TD]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 5"] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"] [/TD]
[TD="colspan: 3"]C[/TD]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 5"] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 5"] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 5"] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 5"] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col span="4"><col><col><col span="2"><col><col><col span="33"></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Table 2
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1119"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Project and Project End[/TD]
[TD]Weld Number[/TD]
[TD]Accept/Reject[/TD]
[TD]Tube OD[/TD]
[TD]Tube WT[/TD]
[TD]Weld Procedure[/TD]
[TD]Welder ID[/TD]
[TD]Weld Machine Number[/TD]
[TD]RT Report File Name[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Project[/TD]
[TD]1[/TD]
[TD]Slag[/TD]
[TD]OD[/TD]
[TD]WT[/TD]
[TD]WPS NO[/TD]
[TD]WID[/TD]
[TD]Machine Number[/TD]
[TD]2014 Weld RT Report.xlsx[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Project[/TD]
[TD]2[/TD]
[TD]Accept[/TD]
[TD]OD[/TD]
[TD]WT[/TD]
[TD]WPS NO[/TD]
[TD]WID[/TD]
[TD]Machine Number[/TD]
[TD]2014 Weld RT Report.xlsx[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Project[/TD]
[TD]3[/TD]
[TD]Accept[/TD]
[TD]OD[/TD]
[TD]WT[/TD]
[TD]WPS NO[/TD]
[TD]WID[/TD]
[TD]Machine Number[/TD]
[TD]2014 Weld RT Report.xlsx[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Project[/TD]
[TD]4[/TD]
[TD]Oxidation[/TD]
[TD]OD[/TD]
[TD]WT[/TD]
[TD]WPS NO[/TD]
[TD]WID[/TD]
[TD]Machine Number[/TD]
[TD]2014 Weld RT Report.xlsx[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Project[/TD]
[TD]6[/TD]
[TD]Accept[/TD]
[TD]OD[/TD]
[TD]WT[/TD]
[TD]WPS NO[/TD]
[TD]WID[/TD]
[TD]Machine Number[/TD]
[TD]2014 Weld RT Report.xlsx[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col span="2"><col><col><col><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Option Explicit
Sub MasterDatabase()
Dim FolderPath As String
Dim FileName As String
Dim pg1 As String
Dim pg2 As String
Dim NR As Long
'Clear sheet
With Sheets("2014")
.Rows(2 & ":" & .Rows.Count).ClearContents
End With
'Change Folder Path
FolderPath = "\\mob-mdat02.no.enterdir.com\HOME_F\214613\Personal\Macro Test\"
'Change Sheetname(s)
pg1 = "Page 1"
pg2 = "Page 2"
'Change File(s)
FileName = Dir(FolderPath & "*.xl*")

Do While FileName <> ""
If FileName <> ThisWorkbook.Name Then


With ThisWorkbook.Sheets("2014")
NR = .Cells(Rows.Count, 1).End(xlUp).Row + 1

'first joint from worksheet "Page 1"
With .Range("A" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AC8"
.Value = .Value
End With
With .Range("B" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!A8"
.Value = .Value
End With
With .Range("C" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!A23"
.Value = .Value
End With
With .Range("D" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!J23"
If .Value = "x" Then
.Value = "Slag"
Else: .Value = "Accept"
End If
End With
With .Range("E" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!S14"
.Value = .Value
End With
With .Range("F" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AA14"
.Value = .Value
End With
With .Range("G" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!S8"
.Value = .Value
End With
With .Range("H" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AE23"
.Value = .Value
End With
With .Range("I" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AK8"
.Value = .Value
.Offset(, 1).Value = FileName
End With
End With


With ThisWorkbook.Sheets("2014")
NR = .Cells(Rows.Count, 1).End(xlUp).Row + 1

'second joint from worksheet "Page 1"
With .Range("C" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!A26"
If .Value = "0" Then
GoTo NextPage
End If
End With
With .Range("A" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AC8"
.Value = .Value
End With
With .Range("B" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!A8"
.Value = .Value
End With
With .Range("D" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!J26"
If .Value = "x" Then
.Value = "Slag"
Else: .Value = "Accept"
End If
End With
With .Range("E" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!S14"
.Value = .Value
End With
With .Range("F" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AA14"
.Value = .Value
End With
With .Range("G" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!S8"
.Value = .Value
End With
With .Range("H" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AE23"
.Value = .Value
End With
With .Range("I" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AK8"
.Value = .Value
.Offset(, 1).Value = FileName
End With
End With


With ThisWorkbook.Sheets("2014")
NR = .Cells(Rows.Count, 1).End(xlUp).Row + 1

'third joint worksheet "Page 1"
With .Range("C" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!A29"
If .Value = "0" Then
GoTo NextPage
End If
End With
With .Range("A" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AC8"
.Value = .Value
End With
With .Range("B" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!A8"
.Value = .Value
End With
With .Range("D" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!J29"
If .Value = "x" Then
.Value = "Slag"
Else: .Value = "Accept"
End If
End With
With .Range("E" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!S14"
.Value = .Value
End With
With .Range("F" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AA14"
.Value = .Value
End With
With .Range("G" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!S8"
.Value = .Value
End With
With .Range("H" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AE23"
.Value = .Value
End With
With .Range("I" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AK8"
.Value = .Value
.Offset(, 1).Value = FileName
End With
End With


With ThisWorkbook.Sheets("2014")
NR = .Cells(Rows.Count, 1).End(xlUp).Row + 1

'fourth joint from worksheet "Page 1"
With .Range("C" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!A32"
If .Value = "0" Then
GoTo NextPage
End If
End With
With .Range("A" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AC8"
.Value = .Value
End With
With .Range("B" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!A8"
.Value = .Value
End With
With .Range("D" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!J32"
If .Value = "x" Then
.Value = "Slag"
Else: .Value = "Accept"
End If
End With
With .Range("E" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!S14"
.Value = .Value
End With
With .Range("F" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AA14"
.Value = .Value
End With
With .Range("G" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!S8"
.Value = .Value
End With
With .Range("H" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AE23"
.Value = .Value
End With
With .Range("I" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AK8"
.Value = .Value
.Offset(, 1).Value = FileName
End With
End With


With ThisWorkbook.Sheets("2014")
NR = .Cells(Rows.Count, 1).End(xlUp).Row + 1

'last joint from worksheet "Page 1"
With .Range("C" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!A35"
If .Value = "0" Then
GoTo NextPage
End If
End With
With .Range("A" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AC8"
.Value = .Value
End With
With .Range("B" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!A8"
.Value = .Value
End With
With .Range("D" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!J35"
If .Value = "x" Then
.Value = "Slag"
Else: .Value = "Accept"
End If
End With
With .Range("E" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!S14"
.Value = .Value
End With
With .Range("F" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AA14"
.Value = .Value
End With
With .Range("G" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!S8"
.Value = .Value
End With
With .Range("H" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AE23"
.Value = .Value
End With
With .Range("I" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg1 & "'!AK8"
.Value = .Value
.Offset(, 1).Value = FileName
End With
End With


'Goto next sheet if second, third, fourth, or last joint = 0
NextPage:


With ThisWorkbook.Sheets("2014")
NR = .Cells(Rows.Count, 1).End(xlUp).Row + 1

'first joint from worksheet "Page 2"
With .Range("A" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg2 & "'!AC8"
.Value = .Value
End With
With .Range("B" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg2 & "'!A8"
.Value = .Value
End With
With .Range("C" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg2 & "'!A23"
.Value = .Value
End With
With .Range("D" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg2 & "'!AJ17"
.Value = .Value
End With
With .Range("E" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg2 & "'!S14"
.Value = .Value
End With
With .Range("F" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg2 & "'!AA14"
.Value = .Value
End With
With .Range("G" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg2 & "'!S8"
.Value = .Value
End With
With .Range("H" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg2 & "'!AE23"
.Value = .Value
End With
With .Range("I" & NR)
.Formula = "='" & FolderPath & "\[" & FileName & "]" & pg2 & "'!AK8"
.Value = .Value
.Offset(, 1).Value = FileName
End With
End With

End If

FileName = Dir
Loop
End Sub
 
You must log in or register to reply here.

Similar threads

S
  • Solved
Transfer of data range (multiple cells) from one sheet to another sheet automatically at the next blank row.
Replies
3
Views
416
Alex Blakenburg
A
L
  • Solved
Send specific data from one sheet to another when a condition is met,
Replies
7
Views
337
mumps
mumps
M
  • Question Question
Auto-populate data from multiple sheets into one master sheet- Help
Replies
1
Views
369
alansidman
alansidman
X
  • Question Question
VBA: Get Data from multiple folder and sub folders
2
Replies
12
Views
4K
JEC
JEC
E
  • Question Question
What's the best way to pull data from this sheet?
Replies
1
Views
331
bobaol
B

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