Merging Multiple Excel Sheets into One Sheet

D

dhfernz

New Member
Joined
Mar 31, 2011
Messages
15
Hi,

I have a workbook with multiple sheets (21 sheets) in it. The header on each sheet begins at row B5. The actual data starts from row B6. The number of rows in each sheet varies and the last row contains the total for all columns that has numbers. I wish to have one sheet that combines data from all the 21 sheets. But;

1) The header should be repeated only once
2) Only the rows that has data should be included, blank rows should be ignored
3) I do not need the last row i.e. total to be considered while merging the sheets

I've given a snapshot of the data below. The first column contains the row number just for reference.

Many thanks in advance - Derick


<table border="0" cellpadding="0" cellspacing="0" width="1162"><col style="width: 35pt;" width="47"> <col style="width: 131pt;" width="175"> <col style="width: 119pt;" width="159"> <col style="width: 65pt;" width="87"> <col style="width: 30pt;" width="40"> <col style="width: 38pt;" width="50"> <col style="width: 30pt;" span="2" width="40"> <col style="width: 34pt;" width="45"> <col style="width: 39pt;" width="52"> <col style="width: 37pt;" width="49"> <col style="width: 30pt;" span="2" width="40"> <col style="width: 41pt;" width="54"> <col style="width: 30pt;" span="2" width="40"> <col style="width: 42pt;" width="56"> <col style="width: 81pt;" width="108"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt; width: 35pt;" height="17" width="47">B5</td> <td class="xl77" style="width: 131pt;" width="175">Name</td> <td class="xl67" style="border-left: medium none; width: 119pt;" width="159">Address</td> <td class="xl67" style="border-left: medium none; width: 65pt;" width="87">Telephone</td> <td class="xl69" style="border-left: medium none; width: 30pt;" width="40">Apr</td> <td class="xl69" style="border-left: medium none; width: 38pt;" width="50">May</td> <td class="xl69" style="border-left: medium none; width: 30pt;" width="40">Jun</td> <td class="xl69" style="border-left: medium none; width: 30pt;" width="40">Jul</td> <td class="xl69" style="border-left: medium none; width: 34pt;" width="45">Aug</td> <td class="xl69" style="border-left: medium none; width: 39pt;" width="52">Sep</td> <td class="xl69" style="border-left: medium none; width: 37pt;" width="49">Oct</td> <td class="xl69" style="border-left: medium none; width: 30pt;" width="40">Nov</td> <td class="xl69" style="border-left: medium none; width: 30pt;" width="40">Dec</td> <td class="xl69" style="border-left: medium none; width: 41pt;" width="54">Jan</td> <td class="xl69" style="border-left: medium none; width: 30pt;" width="40">Feb</td> <td class="xl69" style="border-left: medium none; width: 30pt;" width="40">Mar</td> <td class="xl69" style="border-left: medium none; width: 42pt;" width="56">Total</td> <td class="xl68" style="border-left: medium none; width: 81pt;" width="108">Other Members</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B6</td> <td class="xl78" style="border-top: medium none;">Tony</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">PQR Complex Road 1</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">12345678</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;" align="right">300</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;" align="right">500</td> <td>
</td> <td class="xl75" style="border-top: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">800</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B7</td> <td class="xl78" style="border-top: medium none;">Stany</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">PQR Complex Road 1</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1234567890</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;" align="right">1,000</td> <td>
</td> <td class="xl75">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">1,000</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B8</td> <td class="xl78" style="border-top: medium none;">Manny</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">PQR Complex Road 1</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1234567890</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B9</td> <td class="xl78" style="border-top: medium none;">Tony</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">PQR Complex Road 1</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1234567890</td> <td class="xl75" style="border-top: medium none; border-left: medium none;" align="right">250</td> <td class="xl75" style="border-top: medium none; border-left: medium none;" align="right">250</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;" align="right">250</td> <td class="xl75" style="border-top: medium none; border-left: medium none;" align="right">250</td> <td class="xl75" style="border-top: medium none; border-left: medium none;" align="right">250</td> <td>
</td> <td class="xl75" style="border-top: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">1,250</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B10</td> <td class="xl78" style="border-top: medium none;">Stany</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">PQR Complex Road 1</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1234567890</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B11</td> <td class="xl78" style="border-top: medium none;">Manny</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">PQR Complex Road 1</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1234567890</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;" align="right">1,500</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;" align="right">1,000</td> <td>
</td> <td class="xl75" style="border-top: medium none;" align="right">1,500</td> <td class="xl75" style="border-top: medium none; border-left: medium none;" align="right">500</td> <td>
</td> <td class="xl75" style="border-top: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">4,500</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">Simon</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B12</td> <td class="xl78" style="border-top: medium none;">Tony</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">PQR Complex Road 1</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">12345678</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;" align="right">300</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;" align="right">500</td> <td>
</td> <td class="xl75">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">800</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B13</td> <td class="xl78" style="border-top: medium none;">Stany</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">PQR Complex Road 1</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1234567890</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;" align="right">1,000</td> <td>
</td> <td class="xl75">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">1,000</td> <td class="xl73" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B14</td> <td class="xl78" style="border-top: medium none;">Manny</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">PQR Complex Road 1</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1234567890</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl73" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B15</td> <td class="xl78" style="border-top: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl73" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B16</td> <td class="xl78" style="border-top: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl73" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B17</td> <td class="xl78" style="border-top: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl73" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B18</td> <td class="xl78" style="border-top: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl73" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B19</td> <td class="xl78" style="border-top: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl73" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B20</td> <td class="xl78" style="border-top: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl73" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B21</td> <td class="xl78" style="border-top: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl73" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B22</td> <td class="xl78" style="border-top: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl73" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B23</td> <td class="xl78" style="border-top: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl73" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B24</td> <td class="xl78" style="border-top: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl73" style="border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl79" style="height: 12.75pt;" height="17">B25</td> <td class="xl78" style="border-top: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl74" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl73" style="border-left: medium none;">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl79" style="height: 13.5pt;" height="18">B26</td> <td class="xl72" style="border-top: medium none;">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl76" style="border-top: medium none; border-left: medium none;" align="right">250</td> <td class="xl76" style="border-top: medium none; border-left: medium none;" align="right">1,750</td> <td class="xl76" style="border-top: medium none; border-left: medium none;" align="right">600</td> <td class="xl76" style="border-top: medium none; border-left: medium none;" align="right">1,000</td> <td class="xl76" style="border-top: medium none; border-left: medium none;" align="right">2,250</td> <td class="xl76" style="border-top: medium none; border-left: medium none;" align="right">2,750</td> <td class="xl76" style="border-top: medium none; border-left: medium none;" align="right">750</td> <td class="xl76" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl76" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl76" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl76" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl76" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl76" style="border-top: medium none; border-left: medium none;" align="right">9,350</td> <td class="xl66" style="border-left: medium none;">
</td> </tr> </tbody></table>
moz-screenshot.png
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Try...

Code: 
[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] CombineWorksheets()

    [color=green]'Declare the variables[/color]
    [color=darkblue]Dim[/color] wksCombined [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] CalcMode [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=green]'Change the settings for Calculation and ScreenUpdating[/color]
    [color=darkblue]With[/color] Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = [color=darkblue]False[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]

    [color=green]'Add a new worksheet before the first worksheet in the active workbook[/color]
    [color=darkblue]Set[/color] wksCombined = Worksheets.Add
    
    [color=green]'Name the new worksheet[/color]
    wksCombined.Name = "Combined"
    
    [color=green]'Set up the headers for the new worksheet[/color]
    [color=darkblue]With[/color] Worksheets(2)
        .Range("B5:R5").Copy Destination:=wksCombined.Range("B5")
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=green]'Loop through each worksheet within the active workbook[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] wks [color=darkblue]In[/color] ActiveWorkbook.Worksheets
        [color=green]'Skip the new worksheet[/color]
        [color=darkblue]If[/color] wks.Name <> "Combined" [color=darkblue]Then[/color]
            [color=darkblue]With[/color] wks
                [color=green]'Find the last used row in Column B[/color]
                LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
                [color=green]'Copy the data from the current worksheet to the first available row of the new worksheet starting at Column B[/color]
                .Range("B6", .Cells(LastRow, "R")).Copy Destination:=wksCombined.Cells(wksCombined.Cells.Rows.Count, "B").End(xlUp).Offset(1)
            [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] wks
    
    [color=green]'Restore the settings for Calculation and ScreenUpdating[/color]
    [color=darkblue]With[/color] Application
        .Calculation = CalcMode
        .ScreenUpdating = [color=darkblue]True[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=green]'Display a message indicating that the macro has finished[/color]
    MsgBox "Completed...", vbInformation
    
End [color=darkblue]Sub[/color]
[/font]
 
Upvote 0
Hi Domenic,

Thank you so much for your help. I've been trying to read and do this myself for a long time now... This just works like a charm... One more question if you don't mind. If i do not want the first and the last sheet to merge (leave sheet 1 and 21 out. 2 to 20 should merge), how would the code change?

Thanks once again in advance. - Derick
 
Upvote 0
Try replacing the For Each/Next construct with the following...

Code: 
[font=Verdana]    [color=green]'Loop through each worksheet within the active workbook[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] wks [color=darkblue]In[/color] ActiveWorkbook.Worksheets
        [color=green]'Skip the new worksheet, Sheet1, and Sheet21[/color]
        [color=darkblue]Select[/color] [color=darkblue]Case[/color] wks.Name
            [color=darkblue]Case[/color] "Combined", "Sheet1", "Sheet21"
                [color=green]'Do nothing[/color]
            [color=darkblue]Case[/color] [color=darkblue]Else[/color]
                [color=darkblue]With[/color] wks
                    [color=green]'Find the last used row in Column B[/color]
                    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
                    [color=green]'Copy the data from the current worksheet to the first available row of the new worksheet starting at Column B[/color]
                    .Range("B6", .Cells(LastRow, "R")).Copy Destination:=wksCombined.Cells(wksCombined.Cells.Rows.Count, "B").End(xlUp).Offset(1)
                [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]End[/color] [color=darkblue]Select[/color]
    [color=darkblue]Next[/color] wks
    [/font]
 
Upvote 0
HAI, I refer to first solution. how can add first header to the combine sheet (add to the script). Thank you
 
Upvote 0
@Domenic - your code is BRILLIANT and exactly what i was looking for, thanks for sharing!

One thing i'd like to do to tweak this code - how to add a column (pref the first col A) to show the original SHEET NAME being copied to the merged sheet? would like the original sheet name to show on each row of the combined sheet. Thanks!!
 
Upvote 0
ALSO - how can we do this merge on only VISIBILE sheets? i have a bunch of sheets in my workbook that are hidden and dont want to copy those to the combined output.
 
Upvote 0
Use one more condition before merging data into Combined Sheet with following code:

Code: 
If wks.Visible = xlSheetVisible Then
'Code to Merge Data
End If

The code was excellent but addresses a specific formatted data. You can use Excel Merge Tool to combined any files from any excel version.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
902
epactheactor
E
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
349
dilshod_k
D

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

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