Make copy of data in multiple columns and Transpose data and paste in 1 Column


Hello! Here is the description of my data, and what I am attempting to do... your assistance will be much appreciated. I have also added a spreadsheet with sample data in Sheet 1 and how I would want the Macro to produce output in Sheet 2.

  1. I have Sheet 1 with 10 columns. The headers for columns are as such: Column A = Activity, and the Columns B through J = Task i.e. Task B, Task C ...Task J
  2. Each "Activty" in Column A has at least a "Task" in anyone of the columns B through J
  3. I am attempting to transpose the data in Task B through Task J columns into another Sheet i.e. Sheet 2, and place the "Task" Column data from multiple columns in Sheet 1, to a single column, Column A in Sheet 2
  4. Then I want to make a copy of the Activity from Column A in Sheet 1, and paste it next to the corresponding Tasks from Sheet 1, in Column B of Sheet 2. Depending on the number of rows the Columns Task B through J transposed into, the Activity would need to be copied down through each of corresponding Tasks. 


You have to show some own effort before help can be offered. Post the code you have tried to write yourself.
Variatus (rep: 4864) Sep 18, '19 at 1:25 pm
While that can be done, you would never be able to transpose back the other way.  
KenHobson (rep: 55) Sep 22, '19 at 1:50 pm
Add to Discussion



Hi BegistaanDad 

I have attached a copy of you excel of type XLSM as it has a macro.

As you are new to this site I have tried to comment on the code I have written.

You may or maynot notice that there is no copy or paste just reading and assigning values.

There is also a lack of select and offsets 

This is my prefered methodology as I find it easy to move around sheets by using the "Cells" method rather than range. 

Hope this helps to get you started with Macros. There are excelent samples of code to do all sorts of things on this site so give some of them a try and ask if you strike problems.


Answer the Question

You must create an account to use the forum. Create an Account or Login