VBA - to tranpose row into column based on front cell value

[marll]

Hi all, I wish to have a macro to help create new workbooks based on range of data.
My orginal workbook will have data as below

Name	Group	Speak	Read	Written
Alice	ABC	Good	Good	Normal
Grace	ABC	Normal	Unable	Good
Kelly	BCD	Normal	Normal	Good
Jess	CEF	Unable	Unable	Normal

By click the macro, it could help me generate new workbook template as below

Name	Type	scale
Alice	Speak	Good
Alice	Read	Good
Alice	written	Normal
Grace	Speak	Normal
Grace	Read	Unable
Grace	Written	Good
Kelly	Speak	Normal
Kelly	Read	Normal
Kelly	Written	Good
Jess	Speak	Unable
Jess	Read	Unable
Jess	Written	Normal

Appreciate if anyone can help to create the vba.
Thanks
in advance

[jindon]

Assuming data starts from A1 and no blank row/column

Please Login or Register  to view this content.

[rcm]

Please Login or Register  to view this content.

[rcm]

try this macro, it will write the output at row=2, column =10 please feel free to ask any questions

[marll]

Thanks for your help.

How about if the data start from A1 but those speak, read, written column is from K1 ?
Btw, I wish to generate the result into New Workbook with start from column A1.

Again appreciate your kind assistance

[marll]

Tried and the result slight out of my expectation.
Anyway, thanks for your help

[jindon]

Upload your workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[marll]

Upload your workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks for your advice, uploaded as below

[jindon]

1)
Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

2) try

Please Login or Register  to view this content.

Edit: Code has been edited

[marll]

Thanks, but i wish the after sheet created under new workbooks.
Possible ?

[jindon]

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.