trouble with variable sheet name

Hi all,
I have a workbook that downloads information from the internet and places it on a spreadsheet. That part works fine, but I am also trying to include a filter in the process. I am using a string variable (that may not be the correct term), that is used in the url and also is the name of the sheet where the filtered data is supposed to end up. Abbreviated code as follows (i did not include the query part of the code):

Please Login or Register  to view this content.

I am getting a Run-time error '9' Subscript out of range in the red line of code.

This worked when I tested it with a specific sheet name (e.g. "basicmaterials"), but it doesn't this way.

Thanks.

Here is a way that you can check to see what sheet names don't match up. Just run the code:

Please Login or Register  to view this content.

Thanks for your responses. I ran the code suggested by stnkynts and got no matches, so I assume that if i did have an errant space before or after the name that it would have showed that. However, just to be sure I also checked manually for extra spaces. All good on that count.

I tried the modification suggested by 6StringJazzer but ended up with the same Run-time error. My thinking then was to break that line down to see where the error is occuring. Modified as follows:

Please Login or Register  to view this content.

Run-time error occurred on red line. I also tried: Dim sheetName as String and then sheetName = strFin(Z) thinking that maybe that would push it through, but alas same result.

I honestly cannot see what is wrong with that part. Still stumped.

Hi protonLeah,

I tried this more or less, but got the same error. The modification I had to make was because the the Next Z statement actually needs to be outside the With statement. Perhaps I should have done this earlier, but here is the code in it's entirety:

Please Login or Register  to view this content.

I think the With statement is the way to go, but it resulted in the same error when the Next Z was outside of the last With. Again the query works fine. The idea is that "Raw" is the dump sheet for the data before filtering. Once the filter is complete, then "Raw" is cleared and the next set of data is brought in and will run through the same filter and placed on the sheet with the sector name. Ultimately there will be approximately 6 different filters run on the data before I get to the Next Z. I have not written the other filters yet, since I think if I can get one to work the others will follow suit.

Thanks.

Ok I am an idiot. As I was reviewing my last post, I noticed that I set the value of strFin(z) as the url. Works fine when i use a different string for the url. I am kind of surprised that it didn't kick out an error on that debacle. Anyway, thanks all for your help.

Exactly. Thanks protonLeah. used strFinSite as string for url. pretty much how i felt when i saw it.