Excel VBA: Compley copy & paste

Rene Hanschke

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!

QHarr

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2020-12-2

Comments

0 comments

TOP Ranking

Article

Excel VBA: Compley copy & paste

Excel VBA: Compley copy & paste

pump.io port in URL

How to import an asset in swift using Bundle.main.path() in a react-native native module

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Double spacing in rmarkdown pdf

SQL Server : need add a dot before two last character

C++ 16 bit grayscale gradient image from 2D array

JMeter: Why get error when try to save test plan

JWT gives JsonWebTokenError "invalid token"

How to make thrown errors visible outside of a Promise?

How to tell if iOS Today Widget is being updated in the background?

Calling Doctrine clear() with an argument is deprecated

Capybara Selenium Chrome opens About Google Chrome

How to update azerothcore-wotlk docker container

Adding Ripple Effect to RecyclerView item

mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (111 Connection refused)

Error while applying filter on dataframe - PySpark

Unable to add slack to bluemix project

MyPy fails dataclass argument with optional list of objects type

How can I validate and parse phone numbers to extract their country calling code and area code?

Single Sign-On in Spring by using SAML Extension and Shibboleth

python how to create many-to-many of lists inside one list