Web Query

Set your webquery data to import into a junk sheet, then use cell references to pull that data onto your Report pages that are formatted the way you want. When the data refreshes, it won't be touching your formatted cells on the report page.

You can even hide the junk sheet so that for all appearances, it will still look like it's importing directly to your report page(s).

Well, im not familiar with the excel words you used so can you maybe dumb it down to a quick tutorial? thanks in advance
Edit: let me make the problem more clear....after i import the table, i use text to columns function to separate the contents of the table to suit my liking but whenever i refresh the information just goes back into the original format and doesnt separate how i want it to...

- import the web query onto Sheet1
- on Sheet2 use references to the cells in Sheet1, for example =Sheet1!A1 to pull the content of the cell A1 on Sheet1 to a cell in Sheet2.
- rinse and repeat until you have all the cells you want to display on Sheet2
- format the cells on Sheet2 to suit your liking
- refresh your data on Sheet1 and your data on Sheet2 will also refresh without upsetting the formatting.

got it?

Create a sheet called "import" or something like that.

Redo your webquery onto that sheet.

Turn on the macro recorder and let it record you changing the queried data using your Text To Columns trick. When it's laid out in the columns you like, stop the recorder.

Now go over to your "display" sheet where you want the data to appear nice and properly formatted and use cell references to pull the data onto that sheet.

For instance, if the first piece of data is on sheet "import" in cell A1, on your display sheet just use:

=import!A1

Once you have all the data brought over that way, the last thing is probably to get the macro you recorded to activate itself every time the "import" sheet refreshes the webquery data from the internet. After you get everything set up and your sheets formatted the way you want and the macro recorded, post the book up and I'll help you get the macro rewritten into a Worksheet_Change event macro.

JB,

with a web query, you do not need a Text to Columns operation, since Excel will put stuff into individual cells anyway, so there may not be a need for a macro. To refresh the data, you just right-click anywhere in the data area and select Refresh Data.

When i use cell refrences the actual information in the cell is "=sheet1!A1" and not the value inside the original cell so i cant format it in my way.
And i dont understand how to do the macro. i recorded and used text to columns then stopped recording but still whenever i refresh it all goes back into one column not the 3 separate ones i want.
my sheet is below i cant find the separate macro..

Post up the sheet with web query in it, too and the unchanged data. On Sheet2 mock up the final look. I don't see any particular formatting and the one macro in there now doesn't change anything.

Without getting into your macros, "merging" macros is pretty simple.

Two macros called Macro1() and Macro2(). If you ALWAYS run Macro2() after Macro1(), then you can edit Macro1() and put in this at the bottom

Please Login or Register  to view this content.

You can call macros from within macros easily this way and keep them separate making them easy to run individual pieces manually when wanted.

Alternately, you could just copy all the code from the second macro into the first macro at the bottom, usually a similar result. Me...I'd just use the "Call Macro2" approach.