Compare a list with sheetnames and on match do copy-transpose operations

Raghuveer20

New Member
Joined
Jan 26, 2014
Messages
16
Hi!

I have a problem. I have a Workbook named 'SalesData.xlsx' which contains two sheets 'Demand' and 'Availability'. Column A of these worksheets contains CUSTOMER names and remaining thirteen columns contain month-year data.

The following needs to be done with both sheets i.e. 'Demand' and 'Availability'.
I want to select a CUSTOMER name and check if it is available as a sheet in another workbook 'Summary.xlsm'. When it is available, I want to transpose-copy the month-year data of the CUSTOMER to a defined range (corresponding to month-year) in 'Summary.xlsm' as illustrated below.

Hope! someone has a solution.

Regards


'SalesData.xlsx': Contains Sheets 'Demand' and 'Availability'

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">CUSTOMERS</td><td style="font-weight: bold;text-align: right;;">Apr-14</td><td style="font-weight: bold;text-align: right;;">May-14</td><td style="font-weight: bold;text-align: right;;">Jun-14</td><td style="font-weight: bold;text-align: right;;">Jul-14</td><td style="font-weight: bold;text-align: right;;">Aug-14</td><td style="font-weight: bold;text-align: right;;">Sep-14</td><td style="font-weight: bold;text-align: right;;">Oct-14</td><td style="font-weight: bold;text-align: right;;">Nov-14</td><td style="font-weight: bold;text-align: right;;">Dec-14</td><td style="font-weight: bold;text-align: right;;">Jan-15</td><td style="font-weight: bold;text-align: right;;">Feb-15</td><td style="font-weight: bold;text-align: right;;">Mar-15</td><td style="font-weight: bold;;">Annual</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">AAAA</td><td style="font-weight: bold;text-align: right;;">638</td><td style="font-weight: bold;text-align: right;;">736</td><td style="font-weight: bold;text-align: right;;">755</td><td style="font-weight: bold;text-align: right;;">629</td><td style="font-weight: bold;text-align: right;;">683</td><td style="font-weight: bold;text-align: right;;">834</td><td style="font-weight: bold;text-align: right;;">748</td><td style="font-weight: bold;text-align: right;;">850</td><td style="font-weight: bold;text-align: right;;">614</td><td style="font-weight: bold;text-align: right;;">933</td><td style="font-weight: bold;text-align: right;;">810</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">BBBB</td><td style="text-align: right;;">922</td><td style="text-align: right;;">944</td><td style="text-align: right;;">538</td><td style="text-align: right;;">901</td><td style="text-align: right;;">869</td><td style="text-align: right;;">963</td><td style="text-align: right;;">892</td><td style="text-align: right;;">936</td><td style="text-align: right;;">931</td><td style="text-align: right;;">609</td><td style="text-align: right;;">557</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">CCCC</td><td style="text-align: right;;">758</td><td style="text-align: right;;">918</td><td style="text-align: right;;">799</td><td style="text-align: right;;">676</td><td style="text-align: right;;">753</td><td style="text-align: right;;">840</td><td style="text-align: right;;">637</td><td style="text-align: right;;">563</td><td style="text-align: right;;">517</td><td style="text-align: right;;">660</td><td style="text-align: right;;">500</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">DDDD</td><td style="text-align: right;;">536</td><td style="text-align: right;;">955</td><td style="text-align: right;;">775</td><td style="text-align: right;;">705</td><td style="text-align: right;;">692</td><td style="text-align: right;;">973</td><td style="text-align: right;;">557</td><td style="text-align: right;;">974</td><td style="text-align: right;;">533</td><td style="text-align: right;;">601</td><td style="text-align: right;;">700</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">EEEE</td><td style="text-align: right;;">774</td><td style="text-align: right;;">592</td><td style="text-align: right;;">650</td><td style="text-align: right;;">842</td><td style="text-align: right;;">512</td><td style="text-align: right;;">843</td><td style="text-align: right;;">753</td><td style="text-align: right;;">848</td><td style="text-align: right;;">689</td><td style="text-align: right;;">954</td><td style="text-align: right;;">917</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">FFFF</td><td style="text-align: right;;">724</td><td style="text-align: right;;">911</td><td style="text-align: right;;">594</td><td style="text-align: right;;">602</td><td style="text-align: right;;">869</td><td style="text-align: right;;">819</td><td style="text-align: right;;">693</td><td style="text-align: right;;">832</td><td style="text-align: right;;">942</td><td style="text-align: right;;">665</td><td style="text-align: right;;">900</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">GGGG</td><td style="text-align: right;;">640</td><td style="text-align: right;;">902</td><td style="text-align: right;;">548</td><td style="text-align: right;;">908</td><td style="text-align: right;;">796</td><td style="text-align: right;;">892</td><td style="text-align: right;;">965</td><td style="text-align: right;;">501</td><td style="text-align: right;;">503</td><td style="text-align: right;;">810</td><td style="text-align: right;;">687</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Demand</p><br /><br />


'Summary.xlsm': Contains Sheets 'AAAA', 'BBBB', 'CCCC' and so on

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;color: #632523;;">Month wise sales during the year 2014-15</td><td style="font-weight: bold;text-align: center;color: #632523;;"></td><td style="font-weight: bold;text-align: center;color: #632523;;"></td><td style="font-weight: bold;text-align: center;color: #632523;;"></td><td style="font-weight: bold;text-align: center;color: #632523;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;color: #37441C;;">AAAA</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;color: #37441C;;"></td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;color: #37441C;;"></td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;color: #37441C;;"></td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;color: #37441C;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #37441C;background-color: #D7E4BC;;">Month</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;background-color: #D7E4BC;;">Sales</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;background-color: #D7E4BC;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;background-color: #D7E4BC;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;background-color: #D7E4BC;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #37441C;background-color: #D7E4BC;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;background-color: #D7E4BC;;">Demand</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;background-color: #D7E4BC;;">Availa-
bility </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;background-color: #D7E4BC;;">Surplus(+)/   Deficit(-)</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;background-color: #D7E4BC;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;background-color: #D7E4BC;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;background-color: #D7E4BC;;">(Units)</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;background-color: #D7E4BC;;">(Units)</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;background-color: #D7E4BC;;">(Units)</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;background-color: #D7E4BC;;">(%)</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;;">Apr-14</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">638</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;;">May-14</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">736</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;;">Jun-14</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">755</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;;">Jul-14</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">629</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;;">Aug-14</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">683</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;;">Sep-14</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">834</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;;">Oct-14</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">748</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;;">Nov-14</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">850</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;;">Dec-14</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">614</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;;">Jan-15</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">933</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;;">Feb-15</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">810</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;;">Mar-15</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;;">Annual</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;;">933</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;;">0</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;;">-933</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #37441C;;">-100.0</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">AAAA</p><br /><br />
 
Last edited:

Raghuveer20

New Member
Joined
Jan 26, 2014
Messages
16
As illustrated above, I want to copy columns to right of AAAA from Demand sheet in SalesData.xlsx workbook to Demand Column in sheet AAAA of summary.xlsm workbook
 

Raghuveer20

New Member
Joined
Jan 26, 2014
Messages
16
Hi! I have made the following code with help from some old posts on this site. It is working fine. But, is the code efficient enough? Any suggestions please.

Regards

Code:
Sub Summary()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
Dim ws As Worksheet
Dim lr As Long, c As Range, fLoc As Range, rng As Range, tLoc As Range


Set wb1 = Workbooks("SalesData.xlsx")
Set wb2 = Workbooks("Summary.xlsm")
Set sh1 = wb1.Sheets("Demand")
Set sh2 = wb1.Sheets("Availability")


lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("A2:A" & lr)
For Each ws In wb2.Worksheets
  Set Found = rng.Find(ws.Name, , xlValues, xlWhole, , , False)
        If Not Found Is Nothing Then
            With ws
                Found.Offset(0, 1).Resize(1, 13).Copy
                ws.Range("B6:B18").Resize(13, 1).PasteSpecial Transpose:=True
            End With
            Set Found = Nothing
        End If
Next ws


lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh2.Range("A2:A" & lr)
For Each ws In wb2.Worksheets
  Set Found = rng.Find(ws.Name, , xlValues, xlWhole, , , False)
        If Not Found Is Nothing Then
            With ws
                Found.Offset(0, 1).Resize(1, 13).Copy
                ws.Range("C6:C18").Resize(13, 1).PasteSpecial Transpose:=True
            End With
            Set Found = Nothing
        End If
Next ws
End Sub
 

Raghuveer20

New Member
Joined
Jan 26, 2014
Messages
16
I want to put the following code in a new sub and invoke the sub when needed. How do we do that.
Note: Here ws, sh1, wb2, A2:A, B6:B18 are the variables

Code:
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("A2:A" & lr)
For Each ws In wb2.Worksheets
  Set Found = rng.Find(ws.Name, , xlValues, xlWhole, , , False)
        If Not Found Is Nothing Then
            With ws
                Found.Offset(0, 1).Resize(1, 13).Copy
                ws.Range("B6:B18").Resize(13, 1).PasteSpecial Transpose:=True
            End With
            Set Found = Nothing
        End If
Next ws
 

Forum statistics

Threads
1,082,630
Messages
5,366,656
Members
400,909
Latest member
ola97316

Some videos you may like

This Week's Hot Topics

Top