Macro to rearrange data order from one sheet to another

[crispangilinan]

Hi Excel Gurus. I need your expert guidance on how to write a simple macro that will help to reorganize my raw data from Sheet 1 into Sheet 2. Basically, each data entry on Sheet 1 will be broken down into 8 data points once transferred to Sheet 2.

From this:
Sheet 1 Data

					Full Time	Full Time	Full Time	Full Time	Part Time	Part Time	Part Time	Part Time
Location	Emp ID	Manager	Email	Name	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8
Location 1	100001​	Manager 1	email 1	Employee 1	0	0	0	1	0	0	0	1
Location 2	100002​	Manager 2	email 2	Employee 2	0	0	1	0	0	0	1	0
Location 3	100003​	Manager 3	email 3	Employee 3	0	1	0	0	0	1	0	0
Location 4	100004​	Manager 4	email 4	Employee 4	1	0	1	1	1	0	1	1
Location 5	100005​	Manager 5	email 5	Employee 5	0	0	0	0	0	0	0	0
Location 1	100006​	Manager 6	email 6	Employee 6	0	0	0	0	0	0	0	0
Location 2	100007​	Manager 7	email 7	Employee 7	0	1	0	1	0	1	0	1
Location 3	100008​	Manager 8	email 8	Employee 8	1	0	0	0	1	0	0	0
Location 4	100009​	Manager 9	email 9	Employee 9	0	0	0	0	0	0	0	0
Location 5	100010​	Manager 10	email 10	Employee 10	0	1	1	0	0	1	1	0
Location 1	100011​	Manager 1	email 11	Employee 11	0	0	0	0	0	0	0	0
Location 2	100012​	Manager 2	email 12	Employee 12	0	0	0	0	0	0	0	0
Location 3	100013​	Manager 3	email 13	Employee 13	0	0	0	1	0	0	0	1
Location 4	100014​	Manager 4	email 14	Employee 14	0	0	1	0	0	0	1	0
Location 5	100015​	Manager 5	email 15	Employee 15	0	1	0	0	0	1	0	0
Location 1	100016​	Manager 6	email 16	Employee 16	1	0	0	0	1	0	0	0
Location 2	100017​	Manager 7	email 17	Employee 17	0	0	1	0	0	0	1	0
Location 3	100018​	Manager 8	email 18	Employee 18	0	0	0	1	0	0	0	1
Location 4	100019​	Manager 9	email 19	Employee 19	0	0	0	0	0	0	0	0
Location 5	100020​	Manager 10	email 20	Employee 20	0	1	1	0	0	1	1	0
Location 1	100021​	Manager 1	email 21	Employee 21	0	0	0	0	0	0	0	0
Location 2	100022​	Manager 2	email 22	Employee 22	1	0	1	0	1	0	1	0
Location 3	100023​	Manager 3	email 23	Employee 23	0	0	0	1	0	0	0	1
Location 4	100024​	Manager 4	email 24	Employee 24	0	0	0	0	0	0	0	0
Location 5	100025​	Manager 5	email 25	Employee 25	0	1	0	0	0	1	0	0
Location 1	100026​	Manager 6	email 26	Employee 26	0	0	0	0	0	0	0	0
Location 2	100027​	Manager 7	email 27	Employee 27	1	0	0	0	1	0	0	0
Location 3	100028​	Manager 8	email 28	Employee 28	0	0	0	0	0	0	0	0
Location 4	100029​	Manager 9	email 29	Employee 29	0	1	1	1	0	1	1	1
Location 5	100030​	Manager 10	email 30	Employee 30	0	0	0	0	0	0	0	0

To This:
Sheet 2 Data

Emp ID	Name	Email	Manager	Location	Status	Question	Value
100001​	Employee 1	email 1	Manager 1	Location 1	Full Time	Q1	0
100001​	Employee 1	email 1	Manager 1	Location 1	Full Time	Q2	0
100001​	Employee 1	email 1	Manager 1	Location 1	Full Time	Q3	0
100001​	Employee 1	email 1	Manager 1	Location 1	Full Time	Q4	1
100001​	Employee 1	email 1	Manager 1	Location 1	Part Time	Q5	0
100001​	Employee 1	email 1	Manager 1	Location 1	Part Time	Q6	0
100001​	Employee 1	email 1	Manager 1	Location 1	Part Time	Q7	0
100001​	Employee 1	email 1	Manager 1	Location 1	Part Time	Q8	1
100002​	Employee 2	email 2	Manager 2	Location 2	Full Time	Q1	0
100002​	Employee 2	email 2	Manager 2	Location 2	Full Time	Q2	0
100002​	Employee 2	email 2	Manager 2	Location 2	Full Time	Q3	1
100002​	Employee 2	email 2	Manager 2	Location 2	Full Time	Q4	0
100002​	Employee 2	email 2	Manager 2	Location 2	Part Time	Q5	0
100002​	Employee 2	email 2	Manager 2	Location 2	Part Time	Q6	0
100002​	Employee 2	email 2	Manager 2	Location 2	Part Time	Q7	1
100002​	Employee 2	email 2	Manager 2	Location 2	Part Time	Q8	0
100003​	Employee 3	email 3	Manager 3	Location 3	Full Time	Q1	0
100003​	Employee 3	email 3	Manager 3	Location 3	Full Time	Q2	1
100003​	Employee 3	email 3	Manager 3	Location 3	Full Time	Q3	0
100003​	Employee 3	email 3	Manager 3	Location 3	Full Time	Q4	0
100003​	Employee 3	email 3	Manager 3	Location 3	Part Time	Q5	0
100003​	Employee 3	email 3	Manager 3	Location 3	Part Time	Q6	1
100003​	Employee 3	email 3	Manager 3	Location 3	Part Time	Q7	0
100003​	Employee 3	email 3	Manager 3	Location 3	Part Time	Q8	0

As you can observe, a single line data on Sheet 1 will be broken into 8 data points once transferred to sheet 2. Also, the order of the rows are changed/reorganized. Would appreciate if the macro can be adjusted to cover more data points on Sheet 1 (probable 5000?). Thanks in advance!!

 

[Fluff]

Glad we could help & thanks for the feedback.