Help please. How do I automatically reorganise a data table?

Hi there, could anyone help please.

In the attached spreadsheet I need to be able to automatically take imported data and display it in a different layout. Columns A to D on the IMPORTED DATA tab show the raw, imported CSV data. Using the value in column A on the IMPORTED DATA tab I wish to display columns A to D in each of the 15 tables on the NEWLAYOUT tab. I have filled in all of the data manually to show what the end result should look like.

Can this be done, either from a formula look up or by using a macro?. The conditions of the spreadsheet are as follows:

1) The data needs only to be filled in once when the spreadsheet opens. There is no requirement for the data to be dynamically updated if any of the values change in columns A to D. This said, it would not be a problem if it did as long as it didnt trigger a refresh data external import after every sheet change.

2) It must be triggered by the opening of the spreadsheet rather than a shortcut key or clicking on a worksheet textbox / button. If a macro must be used to achieve this then it would not be a problem to include the data import in the same macro.

3) There will never be more than 10 entries per volume group so there is no risk of data "overflowing" the table to the one below it.

4) The number of entries imported will vary, just as the number of entries per volume group will vary

5) The new layout (columns B to J) will be for display purposes only and will not be edited therefore these cells can contain equations.

For info, I'm currently using Excel 2003.

Many thanks,

Sherlock99.

See attache file, where I added the below code. I added command to run macro so you can use it when you need.
I didn't put automatically macro call when you open the workbook because I fear that the macro can start before data refresh.

Please Login or Register  to view this content.

Regards,
Antonio

Thanks antoka05,

that looks to work really well. With regards the running on open concern, is it possible for your macro to do the data import instead (lets say c:\stats.csv), or are you saying that the macro would continue to run the script before it had completed the import?

My reasons for wanting it to run on open is because this is only one of around ten worksheets (identical layout) that will eventually be included in this spreadsheet. I can develop your macro for the other sheets (I understand code, but I'm very new to writing VB from scratch!)

As well as wanting all ten to import and refresh the table, there will be an 11th sheet with totals from each sheet. If the stats are not updated on opening of the sheet then the totals would be incorrect (unless I had a button on the totals sheet to run all 10 macros !).

Obviously stats are only any good if they are accurate, so if you believe your solution is the best approach then a manual refresh it must be.

Many thanks again,

Sherlock99.

Please could you attach one your csv file so we can add refresh data before move data in the same macro.
Regards,
Antonio

Here you go Antonio (with .txt appended to the CSV to please the website upload rules),

many thanks,

Sherlock99.