Well, when I run a query, it retrieves the identified table from the web page and fills the data in on the worksheet. That part is straightforward enough. After that you just have to arrange the data into something you can work with.

But you're asking how you get the data from the web site in the first place, which leads me to suspect your results are different. What's happening when you run the program? If it's not filling in the data from the web page, what is it doing?

And by the way, what happens when you run that query manually, without VBA? Because after all you're trying to get VBA to to automatically what you can already do by hand, right? So how well does it work when you do it by hand?

I never runned it; I thought there were more steps to it. Now I went back into the file and saw that the formula of L4 was now in K4. I thought the problem of the brackets was fixed, but it's not.

My mistake... sorry :/

I wrote to the support team of Metacritic, but I haven't received any replies unfortunatly...

The error I get is "Invalid web query".

Also, when I do it manually, I don't do it with a query, I copy paste each page individually and make some removal of data manually (it takes quite a while).

Ah, that explains something: When I ran the program I, too, kept getting an invalid Web query. "But" (I thought) "it works for her, so I must be missing something."

However, here's what I found that you can do: You can set up the query manually. After that, your VBA program can do the refresh and reformat the data to suit you. At least, my program could refresh it automatically. Try it:

1) Create the query manually. Now it's stored permanently in the worksheet.
2) Set up a program with just this one statement in it:That's assuming that you have just the one web query in the active worksheet, or at least that we're talking about the first one.
3) Go back to your worksheet and delete or change a few values that the query downloaded when you first set it up.
4) Run the one-statement program I described in step 2.

You should see that the program did the Refresh, which caused the data you changed or deleted to restored; that proves that the program works, even though it doesn't seem to know what to do with those square brackets when you try to get it to set up the query in the first place.

After that, you just have to add to your program whatever statements you need to toss out the data you don't want and arrange the rest into a usable format. If you don't know how to do that, don't despair; you're making progress. Just prove to yourself that the above works, so far, and then come back and we'll talk more.

Thanks again for your help! However, I don't think the above can really work with what I want to do mostly because the website changes each 3 months. That's why there is a formula in K4 (L4 previously).

Also, I need the data from all the different pages.

For example, for the next 3 months, it's going to be the 19 pages : http://www.metacritic.com/search/all/results?cats[game]=1&date_range_from=09-31-2013&date_range_to=12-31-2013&score_range_from=60&search_type=advanced

The 3 months after that, it's going to be X pages (today 3 pages) : http://www.metacritic.com/search/all/results?cats[game]=1&date_range_from=12-31-2013&date_range_to=03-31-2014&score_range_from=60&search_type=advanced

1) Well, wait; of course the content of the web pages changes. But are you saying the layout changes on a regular basis as well? Because if the content changes, but the data are in the same cells each time, then your program can still work with it.

Oh, wait, you mean the URL itself changes? Sure, that makes sense; but still, isn't it better to update the link manually and have the routine do all the reformatting afterward, than to do both jobs by hand?

2) The fact that you have to pull more than one page is more reason to use a VBA routine, not less.

By replying to your post, I just found out that the brackets can be replaced!

On the website, [= %5B & ]= %5D

I now have the first part of the code which works:

Here are the next problems...

How do I automate it to have the correct amount of pages each time? For instance, last 3 months have 19 pages. That means 19 web querys and 19 different webpages? What if next time it's 23 pages?

And how do I make the data to follow suit? For instance, the first page of data copied from the website end on L259 with the first query. The second page should start on L260, but this will change each 3 months. How do I make the query to look for last row of page 1 of data and then choose its destination to follow suit? I'm not sure if I'm clear? lol :P

Doh! <head slap> I knew about the %xx syntax in a URL; I completely failed to think how that would solve your problem. My apologies, Younes.

Ok, next, how to have your program figure out how many "pages" to go after. But how do you know? In fact, what's an example of multiple pages? I'm not sure what we're talking about: Do you do a different search every month and the URL generates the pages, or do you go to a master page and it says that this month there are 14 pages in the listing, or what?

Your second question is clear enough, but I'm inclined to say it'll be simpler, once your program knows there are to be n pages of output, to create n new worksheets, put one query on each, and then have your program pull the data from each in turn and assemble them on master worksheet. Not that it can't be done the other way, but IMO it'll be easier to keep track of the data movements this way. But I want to wait to see what this multiple-page thingy looks like, first.