Hello Expert,
I need your help with this query.
i have 2 sheet. 1st sheet contains 2 column data
2nd sheet also contains 2 column data but 2nd matching column with sheet 1
I want to look for a cell in 2nd sheet and copy all rows from sheet 1 and replace copied rows 1st cell value with a value from 1st cell of 2nd sheet and keep adding rows.
<table style="width: 159px; height: 336px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 61pt;" width="81"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">Sheet1</td> <td style="width: 61pt;" width="81">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ColA</td> <td>Col B</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">abc31</td> <td class="xl63" style="border-left: medium none;">dreamxyz1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc31</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc34</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz7</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc34</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz8</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc34</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz9</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc34</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc36</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc36</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz12</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc36</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz13</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc37</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz14</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc37</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz17</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc37</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz18</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc43</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz19</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc43</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz20</td> </tr> </tbody></table>
=======================================
<table width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">Sheet2</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Col A</td> <td>Col B</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">data1</td> <td class="xl65" style="border-left: medium none;">abc31</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc31</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc31</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc34</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc34</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data6</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc36</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data7</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc37</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data8</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc37</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data9</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc37</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data10</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc37</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data11</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc37</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data12</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc37</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data13</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc43</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data14</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc43</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data15</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc43</td> </tr> </tbody></table>=======================================================
Final requested result
<table width="159" border="0" cellpadding="0" cellspacing="0"><col style="width: 59pt;" width="78"> <col style="width: 61pt;" width="81"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 59pt;" width="78" height="20">Final Sheet</td> <td style="width: 61pt;" width="81">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Column A</td> <td>Column B</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">data1</td> <td class="xl65" style="border-left: medium none;">dreamxyz1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz7</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz8</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz9</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz7</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz8</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz9</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data6</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data6</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz12</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data6</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz13</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">............contd..........................</td> <td>
</td> </tr> </tbody></table>
request you to help me to achieve this via macro
I need your help with this query.
i have 2 sheet. 1st sheet contains 2 column data
2nd sheet also contains 2 column data but 2nd matching column with sheet 1
I want to look for a cell in 2nd sheet and copy all rows from sheet 1 and replace copied rows 1st cell value with a value from 1st cell of 2nd sheet and keep adding rows.
<table style="width: 159px; height: 336px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 61pt;" width="81"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">Sheet1</td> <td style="width: 61pt;" width="81">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ColA</td> <td>Col B</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">abc31</td> <td class="xl63" style="border-left: medium none;">dreamxyz1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc31</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc34</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz7</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc34</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz8</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc34</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz9</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc34</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc36</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc36</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz12</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc36</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz13</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc37</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz14</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc37</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz17</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc37</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz18</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc43</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz19</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">abc43</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">dreamxyz20</td> </tr> </tbody></table>
=======================================
<table width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">Sheet2</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Col A</td> <td>Col B</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">data1</td> <td class="xl65" style="border-left: medium none;">abc31</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc31</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc31</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc34</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc34</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data6</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc36</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data7</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc37</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data8</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc37</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data9</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc37</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data10</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc37</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data11</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc37</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data12</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc37</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data13</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc43</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data14</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc43</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data15</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">abc43</td> </tr> </tbody></table>=======================================================
Final requested result
<table width="159" border="0" cellpadding="0" cellspacing="0"><col style="width: 59pt;" width="78"> <col style="width: 61pt;" width="81"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 59pt;" width="78" height="20">Final Sheet</td> <td style="width: 61pt;" width="81">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Column A</td> <td>Column B</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">data1</td> <td class="xl65" style="border-left: medium none;">dreamxyz1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz7</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz8</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz9</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz7</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz8</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz9</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data6</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data6</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz12</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">data6</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">dreamxyz13</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">............contd..........................</td> <td>
</td> </tr> </tbody></table>
request you to help me to achieve this via macro
