Macro to copy neighboring columns based on value of a reference cell

Hi,

First I'd like to thank everyone who contributes on this forum. I have learned an amazing amount of information about excel just from searching other threads and am constantly impressed with the speed and quality of the responses. I hope that I can get some help with a project that I'm working on.

I'm trying to create a macro to copy the data from 2 columns (B and C) based on the value in A.

I've attached a sheet with what I've got so far. There is macro already to pull the unique values and then another to insert a couple blank lines between each row. I can't figure out how to then copy and paste special-transpose the values depending on the value in Column A. I hope that makes sense... The reason for the change in formatting is to allow the data to be copied into a separate sheet which automatically puts it into charts for comparison.

The first few rows of Sheet3 show what I hope the eventual output will look like.

Thanks in advance and if anything is unclear, please let me know.

Graham

Thanks to both Joakim and AlphaFrog for these solutions. They both work very well and have saved me a lot of time.

Is it possible to make an adjustment to the macro to allow the original data to be filtered? The data would be filtered based on the values in column A and the output would need to show the results from only the filtered cells.

Thanks!

I'm still hoping to find a way to copy only the filtered cells if anyone has any thoughts... Thanks

Thanks, Joakim! That works perfectly! Out of curiosity, why did you use the xlpinyin sort method? Isn't that just for sorting Chinese characters?

Figured it out