Combine data from multiple worksheets to master spreadsheet

[Renny0927]

I have a workbook that contains about 100 worksheets that house data I need to combine into one master file. Each of the worksheets are formatted the same with the data contained in the same column(s)/row(s). I need to take the info and create a row of information for each client. The Data column, I would like to transpose so the data is in a row and the client name would be the first column and then the last column would be Average Raw Score. I have attached a sample worksheet that contains the current layout and the layout I would like to have on a master worksheet. I tried to add an attachment but I am not allowed to on this site, no idea why.

Data resides D10:D18
Client name - B2
Average Raw Score - B33

Any help on this will be greatly appreciated!

REnny

[TABLE="width: 1254"]
<tbody>[TR]
[TD="width: 230, bgcolor: transparent"][/TD]
[TD="width: 167, bgcolor: transparent"][/TD]
[TD="width: 139, bgcolor: transparent"]RISK RATING MATRIX[/TD]
[TD="width: 158, bgcolor: transparent"][/TD]
[TD="width: 112, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]COMPOSITE RISK RATING[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Borrower Name[/TD]
[TD="bgcolor: #FFFF00"]smith Realty[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Date [/TD]
[TD="bgcolor: transparent"]8/31/2016[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Business Name[/TD]
[TD="bgcolor: transparent"]business name[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Loan Officer[/TD]
[TD="width: 176, bgcolor: transparent, colspan: 2"]loan officer name[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Loan #[/TD]
[TD="bgcolor: transparent"]S1403[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 694, bgcolor: transparent, colspan: 4"]1) Enter data into Table 1 Column D (Data) and Column E (Date Reported). Score is calculated automatically.[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 536, bgcolor: transparent, colspan: 3"]2) Using Table 2 Average Raw Score, round up or down according to potential risk of loan.[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 694, bgcolor: transparent, colspan: 4"]3) Assign a composite risk rating based on a combination of quantitative and qualitative assessment.[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]TABLE 1 QUANTITATIVE ASSESSMENT[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Credit Criteria[/TD]
[TD="bgcolor: transparent"]Indicator[/TD]
[TD="bgcolor: transparent"]Source[/TD]
[TD="bgcolor: transparent"]Data[/TD]
[TD="bgcolor: transparent"]Date Reported[/TD]
[TD="bgcolor: transparent"]Score[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 230, bgcolor: transparent"]Loan to Value[/TD]
[TD="bgcolor: transparent"]Total Loans Outstanding[/TD]
[TD="bgcolor: transparent"]DownHome[/TD]
[TD="bgcolor: #FFFF00"]$20,869[/TD]
[TD="bgcolor: transparent"]8/31/2016[/TD]
[TD="width: 64, bgcolor: transparent"]29.1%[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Collateral Value[/TD]
[TD="bgcolor: transparent"]Green collateral files[/TD]
[TD="bgcolor: #FFFF00"]$71,750[/TD]
[TD="bgcolor: transparent"]1/8/2010[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 230, bgcolor: transparent"]Credit Score[/TD]
[TD="bgcolor: transparent"]FICO Score (Transunion)[/TD]
[TD="bgcolor: transparent"]Transunion†[/TD]
[TD="bgcolor: #FFFF00"]$683[/TD]
[TD="bgcolor: transparent"]2011[/TD]
[TD="bgcolor: transparent"]683[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 230, bgcolor: transparent"]Debt Service Coverage Ratio (Including Loan)[/TD]
[TD="bgcolor: transparent"]Yearly Operating Income[/TD]
[TD="bgcolor: transparent"]Financial statements*[/TD]
[TD="bgcolor: #FFFF00"]$96,651[/TD]
[TD="bgcolor: transparent"]2013[/TD]
[TD="width: 64, bgcolor: transparent"]4.474584722[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Yearly Loan Payments[/TD]
[TD="bgcolor: transparent"]DownHome[/TD]
[TD="bgcolor: #FFFF00"]$21,600[/TD]
[TD="bgcolor: transparent"]2016[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 230, bgcolor: transparent"]Current Ratio[/TD]
[TD="bgcolor: transparent"]Current Assets[/TD]
[TD="bgcolor: transparent"]Financial statements*[/TD]
[TD="bgcolor: #FFFF00"]$127,582[/TD]
[TD="bgcolor: transparent"]12/31/2012[/TD]
[TD="bgcolor: transparent"]0.846[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 230, bgcolor: transparent"]Net Worth[/TD]
[TD="bgcolor: transparent"]Current Liabilities[/TD]
[TD="bgcolor: transparent"]Financial statements*[/TD]
[TD="bgcolor: #FFFF00"]$150,780[/TD]
[TD="bgcolor: transparent"]12/31/2012[/TD]
[TD="bgcolor: transparent"]-23198.47[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 230, bgcolor: transparent"]Profitability (# consecutive years)[/TD]
[TD="width: 167, bgcolor: transparent"]# Years that (Income - Expenses) > $0[/TD]
[TD="bgcolor: transparent"]Financial statements*[/TD]
[TD="bgcolor: #FFFF00"]1[/TD]
[TD="bgcolor: transparent"]2013[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 230, bgcolor: transparent"]Time in Business[/TD]
[TD="bgcolor: transparent"]# Years[/TD]
[TD="bgcolor: transparent"]DownHome[/TD]
[TD="bgcolor: #FFFF00"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 998, bgcolor: transparent, colspan: 8"]† If borrower has a FICO score on file that is < 3 years old, this should be used. If their FICO score is > 3 years old, a new soft pull credit check should be run.[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 806, bgcolor: transparent, colspan: 5"]* Alternately tax returns, income statements and balance sheets can be used.[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]TABLE 2 RISK RATING MATRIX[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 230, bgcolor: transparent"]Credit Criteria[/TD]
[TD="width: 167, bgcolor: transparent"]Risk Rating[/TD]
[TD="width: 139, bgcolor: transparent"]Risk Rating 1[/TD]
[TD="width: 158, bgcolor: transparent"]Risk Rating 2[/TD]
[TD="width: 112, bgcolor: transparent"]Risk Rating 3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 230, bgcolor: transparent"]Loan to Value[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]60% and below[/TD]
[TD="bgcolor: transparent"]61%-79%[/TD]
[TD="bgcolor: transparent"]80% and above[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 230, bgcolor: transparent"]Credit Score[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]750-850[/TD]
[TD="bgcolor: transparent"]700-749[/TD]
[TD="bgcolor: transparent"]650-699[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 230, bgcolor: transparent"]Debt Service Coverage Ratio[/TD]
[TD="width: 167, bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]>1.31[/TD]
[TD="bgcolor: transparent"]1.21 - 1.3[/TD]
[TD="bgcolor: transparent"]1.1 - 1.2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 230, bgcolor: transparent"]Current Ratio[/TD]
[TD="width: 167, bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]>1.51[/TD]
[TD="bgcolor: transparent"]1.11-1.50[/TD]
[TD="bgcolor: transparent"]1.00-1.10[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 230, bgcolor: transparent"]Net Worth[/TD]
[TD="width: 167, bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]Over $50K[/TD]
[TD="bgcolor: transparent"]$25K-50K[/TD]
[TD="bgcolor: transparent"]<$25K[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 230, bgcolor: transparent"]Profitability[/TD]
[TD="width: 167, bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]>3 years[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 230, bgcolor: transparent"]Time in Business[/TD]
[TD="width: 167, bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]>5 years[/TD]
[TD="bgcolor: transparent"]3-5 years[/TD]
[TD="bgcolor: transparent"]<3 years[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Total Raw Score[/TD]
[TD="bgcolor: transparent"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Average Raw Score[/TD]
[TD="bgcolor: #FFFF00"]2.1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]TABLE 3 QUALITATIVE ASSESSMENT[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Character[/TD]
[TD="bgcolor: transparent"]Capacity[/TD]
[TD="bgcolor: transparent"]Capital[/TD]
[TD="bgcolor: transparent"]Collateral[/TD]
[TD="bgcolor: transparent"]Cash Flow[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Current Layout per worksheet above[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 397, bgcolor: transparent, colspan: 2"]This is the layout I would like to have on a master[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Client Name[/TD]
[TD="bgcolor: transparent"]Total Loans Outstanding[/TD]
[TD="bgcolor: transparent"]Collateral Value[/TD]
[TD="bgcolor: transparent"]FICO Score (Transunion)[/TD]
[TD="bgcolor: transparent"]Yearly Operating Income[/TD]
[TD="bgcolor: transparent"]Yearly Loan Payments[/TD]
[TD="bgcolor: transparent"]Current Assets[/TD]
[TD="bgcolor: transparent"]Current Liabilities[/TD]
[TD="width: 64, bgcolor: transparent"]# Years that (Income - Expenses) > $0[/TD]
[TD="bgcolor: transparent"]# Years[/TD]
[TD="bgcolor: transparent"]Average Raw Score[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]smith realty[/TD]
[TD="bgcolor: #FFFF00"]$20,869[/TD]
[TD="bgcolor: #FFFF00"]$71,750[/TD]
[TD="bgcolor: #FFFF00"]$683[/TD]
[TD="bgcolor: #FFFF00"]$96,651[/TD]
[TD="bgcolor: #FFFF00"]$21,600[/TD]
[TD="bgcolor: #FFFF00"]$127,582[/TD]
[TD="bgcolor: #FFFF00"]$150,780[/TD]
[TD="bgcolor: #FFFF00"]1[/TD]
[TD="bgcolor: #FFFF00"]6[/TD]
[TD="bgcolor: #FFFF00"]2.1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[sijpie]

Hi Renny,

This code will do it for you.

It is a bit more complicated because I use arrays to read each sheet in one read operation and gather the output to write it to the output sheet in one write operation. Read and write operations are relatively slow in Excel, so if you have hundreds of sheets, doing it differently could be slow. This way it is so fast, you won't even believe it did run.

i also added some code incase there is no summary sheet, it will create one.

Read the comments to get a bit of understanding.

Let me know how it goes.

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> Summarise()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsWS <SPAN style="color:#00007F">As</SPAN> Worksheet, wsSum <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> lROut <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lRIn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lShtCnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> vOut <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, vDat <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> sSummSheetName = "Summary"    <SPAN style="color:#007F00">' <<< Summary sheet name - modify as required</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> lNumCols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 11         <SPAN style="color:#007F00">' <<< 11 columns wide (A-K). If at later stage _<br>                                              more columns added then increase this number</SPAN><br>    <br>    <br>    <SPAN style="color:#007F00">' count the number of sheets, add 1:</SPAN><br>    lShtCnt = Worksheets.Count + 1<br>    <SPAN style="color:#007F00">' size the output array. We are using an _<br>      array to make this macro blindingly fast _<br>      with only one write to the summary sheet</SPAN><br>    <SPAN style="color:#00007F">ReDim</SPAN> vOut(1 <SPAN style="color:#00007F">To</SPAN> lShtCnt, 1 <SPAN style="color:#00007F">To</SPAN> lNumCols)  <SPAN style="color:#007F00">' 11 columns wide (A-K)</SPAN><br>    lRIn = 1<br>    <br>    <br>    <SPAN style="color:#007F00">' Set the output sheet</SPAN><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wsSum = Sheets(sSummSheetName)<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <br>    <SPAN style="color:#00007F">If</SPAN> wsSum <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">' no summary sheet yet, create</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> wsSum = Sheets.Add(before:=Sheets(1))<br>        <SPAN style="color:#00007F">With</SPAN> wsSum<br>            .Name = sSummSheetName<br>            .Range("A1:K1").ColumnWidth = 18<br>            .Columns("K").Font.Bold = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#007F00">' Enter the headings into the output array</SPAN><br>        vOut(1, 1) = "Client Name"<br>        vOut(1, 2) = "Total Loans Outstanding"<br>        vOut(1, 3) = "Collateral Value"<br>        vOut(1, 4) = "FICO Score (Transunion)"<br>        vOut(1, 5) = "Yearly Operating Income"<br>        vOut(1, 6) = "Yearly Loan Payments"<br>        vOut(1, 7) = "Current Assets"<br>        vOut(1, 8) = "Current Liabilities"<br>        vOut(1, 9) = "# Years that (Income - Expenses) > $0"<br>        vOut(1, 10) = "# Years"<br>        vOut(1, 11) = "Average Raw Score"<br>        lRIn = 2<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>      <br>    <SPAN style="color:#007F00">' Copy the data for each sheet to output array</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wsWS <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Sheets<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> wsWS <SPAN style="color:#00007F">Is</SPAN> wsSum <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">' read the sheet into an array for faster processing</SPAN><br>        vDat = wsWS.Range("A1:D33")<br>        <SPAN style="color:#007F00">' now A1 is vDat(1,1) and B33 is vDat(33,2)</SPAN><br>        <br><SPAN style="color:#007F00">' Client Name | Total Loans | Collateral Value |FICO Score | Yearly Op Income | Yearly Loan Paym |Current Assets | Current Liabilities | # Years(Inc - Exp) > $0 |  # Years Average Raw Score</SPAN><br>        <br>        <SPAN style="color:#007F00">' write the data into the output array</SPAN><br>        vOut(lRIn, 1) = vDat(2, 2) <SPAN style="color:#007F00">' name in B2</SPAN><br>        vOut(lRIn, 2) = vDat(10, 4) <SPAN style="color:#007F00">' Tot Loans - D4</SPAN><br>        vOut(lRIn, 3) = vDat(11, 3) <SPAN style="color:#007F00">' Coll Valu - C11</SPAN><br>        vOut(lRIn, 4) = vDat(12, 4) <SPAN style="color:#007F00">' Fic Score - D12</SPAN><br>        vOut(lRIn, 5) = vDat(13, 4) <SPAN style="color:#007F00">' yearl income - D13</SPAN><br>        vOut(lRIn, 6) = vDat(14, 3) <SPAN style="color:#007F00">' Y Loan Paym - C14</SPAN><br>        vOut(lRIn, 7) = vDat(15, 4) <SPAN style="color:#007F00">' Curr Assts - D15</SPAN><br>        vOut(lRIn, 8) = vDat(16, 4) <SPAN style="color:#007F00">' Curr Liab - D16</SPAN><br>        vOut(lRIn, 9) = vDat(17, 4) <SPAN style="color:#007F00">' Y >$0 - D17</SPAN><br>        vOut(lRIn, 10) = vDat(18, 4) <SPAN style="color:#007F00">' #Y - D18</SPAN><br>        vOut(lRIn, 11) = vDat(33, 2) <SPAN style="color:#007F00">' Score B33</SPAN><br>        <SPAN style="color:#007F00">' increment row counter</SPAN><br>        lRIn = lRIn + 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> wsWS<br>    <br>        <SPAN style="color:#007F00">' Get 1st empty row on Summary sheet</SPAN><br>    lROut = wsSum.Cells(Rows.Count, 1).End(xlUp).Row + 1<br>    <SPAN style="color:#007F00">' Write the output to the summary sheet</SPAN><br>    wsSum.Cells(lROut, 1).Resize(lShtCnt, lNumCols).Value = vOut<br><br>    <SPAN style="color:#007F00">' clean up</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wsSum = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>