Excel VBA: Compley copy & paste

Question

as many of us, I'm new to VBA. Often I can research the answers to my questions, but I suppose this on is too specific. I got the following arrangement of data:

<table border=0 cellpadding=0 cellspacing=0 width=491 style='border-collapse:
 collapse;table-layout:fixed;width:368pt'>
 <col width=107 style='mso-width-source:userset;mso-width-alt:3913;width:80pt'>
 <col width=64 span=6 style='width:48pt'>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl1523206 width=107 style='height:15.0pt;width:80pt'>Production site</td>
  <td class=xl1523206 width=64 style='width:48pt'>Year</td>
  <td class=xl1523206 width=64 style='width:48pt'>Part 1</td>
  <td class=xl1523206 width=64 style='width:48pt'> Part 2</td>
  <td class=xl1523206 width=64 style='width:48pt'>Part 3</td>
  <td class=xl1523206 width=64 style='width:48pt'>Part 4</td>
  <td class=xl1523206 width=64 style='width:48pt'>Part n</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
  <td class=xl1523206 align=right>2017</td>
  <td class=xl1523206 align=right>7</td>
  <td class=xl1523206 align=right>4</td>
  <td class=xl1523206 align=right>2</td>
  <td class=xl1523206 align=right>5</td>
  <td class=xl1523206 align=right>3</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
  <td class=xl1523206 align=right>2018</td>
  <td class=xl1523206 align=right>8</td>
  <td class=xl1523206 align=right>6</td>
  <td class=xl1523206 align=right>2</td>
  <td class=xl1523206 align=right>5</td>
  <td class=xl1523206 align=right>3</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
  <td class=xl1523206 align=right>2019</td>
  <td class=xl1523206 align=right>8</td>
  <td class=xl1523206 align=right>6</td>
  <td class=xl1523206 align=right>2</td>
  <td class=xl1523206 align=right>5</td>
  <td class=xl1523206 align=right>3</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
  <td class=xl1523206 align=right>2017</td>
  <td class=xl1523206 align=right>4</td>
  <td class=xl1523206 align=right>3</td>
  <td class=xl1523206 align=right>1</td>
  <td class=xl1523206 align=right>2</td>
  <td class=xl1523206 align=right>0</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
  <td class=xl1523206 align=right>2018</td>
  <td class=xl1523206 align=right>7</td>
  <td class=xl1523206 align=right>4</td>
  <td class=xl1523206 align=right>1</td>
  <td class=xl1523206 align=right>2</td>
  <td class=xl1523206 align=right>0</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
  <td class=xl1523206 align=right>2019</td>
  <td class=xl1523206 align=right>7</td>
  <td class=xl1523206 align=right>4</td>
  <td class=xl1523206 align=right>1</td>
  <td class=xl1523206 align=right>2</td>
  <td class=xl1523206 align=right>0</td>
 </tr>
 <![if supportMisalignedColumns]>
 <tr height=0 style='display:none'>
  <td width=107 style='width:80pt'></td>
  <td width=64 style='width:48pt'></td>
  <td width=64 style='width:48pt'></td>
  <td width=64 style='width:48pt'></td>
  <td width=64 style='width:48pt'></td>
  <td width=64 style='width:48pt'></td>
  <td width=64 style='width:48pt'></td>
 </tr>
 <![endif]>
</table>

The number of parts, production sites and years is not limited. Now I need to copy the data and put it into a different structure to be able to work with it.

This is how the data must look like:

<table border=0 cellpadding=0 cellspacing=0 width=491 style='border-collapse:
 collapse;table-layout:fixed;width:368pt'>
 <col width=107 style='mso-width-source:userset;mso-width-alt:3913;width:80pt'>
 <col width=64 span=6 style='width:48pt'>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl1523206 width=107 style='height:15.0pt;width:80pt'>Part</td>
  <td class=xl1523206 width=64 style='width:48pt'>Production Site</td>
  <td class=xl1523206 width=64 style='width:48pt'>2017</td>
  <td class=xl1523206 width=64 style='width:48pt'>2018</td>
  <td class=xl1523206 width=64 style='width:48pt'>2019</td>
  <td class=xl1523206 width=64 style='width:48pt'></td>
  <td class=xl1523206 width=64 style='width:48pt'>Year n</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
  <td class=xl1523206 align=right>Site A</td>
  <td class=xl1523206 align=right>7</td>
  <td class=xl1523206 align=right>4</td>
  <td class=xl1523206 align=right>2</td>
  <td class=xl1523206 align=right>5</td>
  <td class=xl1523206 align=right>3</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
  <td class=xl1523206 align=right>Site B</td>
  <td class=xl1523206 align=right>8</td>
  <td class=xl1523206 align=right>6</td>
  <td class=xl1523206 align=right>2</td>
  <td class=xl1523206 align=right>5</td>
  <td class=xl1523206 align=right>3</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
  <td class=xl1523206 align=right>Site n</td>
  <td class=xl1523206 align=right>8</td>
  <td class=xl1523206 align=right>6</td>
  <td class=xl1523206 align=right>2</td>
  <td class=xl1523206 align=right>5</td>
  <td class=xl1523206 align=right>3</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
  <td class=xl1523206 align=right>Site A</td>
  <td class=xl1523206 align=right>4</td>
  <td class=xl1523206 align=right>3</td>
  <td class=xl1523206 align=right>1</td>
  <td class=xl1523206 align=right>2</td>
  <td class=xl1523206 align=right>0</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
  <td class=xl1523206 align=right>Site B</td>
  <td class=xl1523206 align=right>7</td>
  <td class=xl1523206 align=right>4</td>
  <td class=xl1523206 align=right>1</td>
  <td class=xl1523206 align=right>2</td>
  <td class=xl1523206 align=right>0</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
  <td class=xl1523206 align=right>Site n</td>
  <td class=xl1523206 align=right>7</td>
  <td class=xl1523206 align=right>4</td>
  <td class=xl1523206 align=right>1</td>
  <td class=xl1523206 align=right>2</td>
  <td class=xl1523206 align=right>0</td>
 </tr>
 <![if supportMisalignedColumns]>
 <tr height=0 style='display:none'>
  <td width=107 style='width:80pt'></td>
  <td width=64 style='width:48pt'></td>
  <td width=64 style='width:48pt'></td>
  <td width=64 style='width:48pt'></td>
  <td width=64 style='width:48pt'></td>
  <td width=64 style='width:48pt'></td>
  <td width=64 style='width:48pt'></td>
 </tr>
 <![endif]>
</table>

I suppose I need to do all kind of indexing, looping and so on. Anybody with ideas to get my head around this task? I appreciate any input. Thanks a lot!

Answer 1

This can be done using Powerquery. You are unpivoting Part and pivoting on year.

Load the table using data > from table to import the source, in Excel 2013 use the powerquery add-in tab (free download from microsoft), in the editor window tha opens you then right clicking on columns to pivot/unpivot (or via transform tab) and finally close and load to sheet.