Formatting data and using macro for text to columns feature

[murali18]

Hi All,

I have a sample xl file having data as shown below:
____________________________________________________________
| Column A | Column B| Column C |
| H1 H2 H3 H4 H5 H6 | | |
| D1 D2 D3 D4 D5 D6 | | |
| H7 H8 H9 | | |
| D7 D8 D9 | | |
| ------------------------------ | | |
Next set similar as above

{Where H means Headers and D means Data (corresponding to the Hs).}

I needed help with macro such that the resulting data is formatted like this.

___________________________________________________________
| Col A | Col B | Col C | Col D | Col E | Col F| Col G|Col H|Col I|Col J |
| H1 | H2 | H3 | H4 | H5 | H6 | H7 | H8 | H9 |
| D1 | D2 | D3 | D4 | D5 | D6 | D7 | D8 | D9 |
| D1 | D2 and so on....


Please help me with the Macro that can format in the above mentioned way. Many Thanks for your help!

P.S - Please let me know if this is inadequate data.

 

[pbornemeier]

Welcome to the board!

Please use Excel Jeanie (see link in my sig) to post the first dozen rows of your worksheets in both their current and desired states.
Are the headers/data always grouped in rows of 6?
Is there only 1 line of data for each header row?
Is there ever more than 9 columns in the end product?