Help Organizing Data Pulled from Web Query

I need some help transposing data I am getting from a simple web query into a more organized form.

Take a look:
https://www.dropbox.com/s/wo1tdpbhp7...ry%20Test.xlsx

The data that I am getting from the query is displayed on Sheet1 (take a look at the query to get an idea of what the source table looks like). I want to take that data and organize it so that it neatly and automatically fills into the columns I have set up in Sheet2.

Is there a way of taking the individual cells from the table import of the query and organizing them on Sheet 2?

An important note: the table that is selected by the web query is updated regulary - new entries (studies) are added and removed periodically. I need it so that the organized table in Sheet2 is automatically populated when that does happen.

Thank you!

Originally Posted by alansidman

The following code will re-organize the data in Sheet 1 and place it on Sheet 3.

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Worked perfectly! Thanks!

I know this is an old thread, but I'm wondering if there is a way to customize this macro to adjust https://www.dropbox.com/s/zcr2n9cyb9...sting.iqy?dl=0 ? When I bring this web query into Excel, it's organized enough to manually cut and paste everything as I want, but I'm wondering if there is a way to automate this with this macro to have columns of the information like the attached image.

Screen Shot 2018-01-09 at 9.41.46 AM.png

Thanks in advance! Any help would be greatly appreciated!