Copy data from one sheet (multiple cells) to another sheet in tabular form

Hi there

I want to copy data from one sheet to another. I managed (with help from an ExcelForum user) to set this up for another project recently. However, the requirements for my latest project are slightly different. The data is on the sheet is not entered in tabular form (it's in various cells on the sheet) but the data needs to be stored in tabular form. I've tried numerous VBA codings from the web but kept coming across two problems - either the data copied across numerous rows (i.e. downwards instead of across) or the data copied correctly the first time, then missed the first cell in the row, then missed another cell and so on. I decided it would be easier to duplicate the cells from the first tab to a second tab where it would appear in tabular form. Then copy the cells from the second tab to the archive tab but I'm wondering if this is a longwinded way.

I've attached a sample file which hopefully makes it easier. Any help much appreciated.

Thanks jaslake. This solution worked perfectly. Great that you managed to transfer the data directly (without having to copy it to the spare sheet).

Much appreciated.