Cycle through worksheets code not quite right

Hello everyone. I am trying to cycle through around 20-30 sheets in a workbook and do a find and replace for links pulled in an array. I want to skip the first two, so basically start at 3. I will always start at worksheet 3, though the name for worksheet 3 may change.

When I enter this code, nothing happens:

Please Login or Register  to view this content.

However if I put
'Sheets(wsCount).Select' after the Set 'ws = Sheets(wsCount)' line, it works through most sheets then at the end it says "Select Method Of Worksheet class failed."

I know this code is not quite right - any suggestions? At least how do I get rid of that error?

If you have any efficiency increases (like you don't think the loop is good) that is fine too, I'm open to criticism.

Thanks!

maybe so (not tested)

Please Login or Register  to view this content.

Arlette,

Still getting the error. It occurs when trying to update the very last tab.For more info, I have 9 worksheets but want to start at 3 and go to 9 (want this to be dynamic as more sheets may be added over time.) When I step through, for some reason it goes past the "count" and starts looking for a worksheet 10, which doesn't exist. I clearly count 9. Why would it be looking for a 10th if it should be counting?

send file pls

Nilem,

A good approach but the problem is that I'm not replacing that range. I've used the range on an input ws (one of the ones I am trying to skip) to fill an array of worksheet names, then selecting each worksheet via the array in the input and then just searching the worksheet for the value to replace. Still lost on why it's trying to count the extra worksheet.

John, huge apologies but due to the sensitive nature of the work I can't share the file. It would give you inside info and rich and get me jail or fired . Believe me, I would have shared from the start if I could have.

Interestingly, I played with this a bit and using some code I was able to trap the names of all the spreadsheets in an array and then display them - I found there was a 10th sheet that isn't even hidden since I don't have the chance called LinkingMetaData. A quick search doesn't turn up much info. What could this be? Could it be do to all the external links I have?

No, it's just a link from a bunch of external Excel files. I don't see anything about it on Google. It's at the end so I can temporarily fix it by getting rid of the Worksheets.Count and just putting in the actual number of worksheets one short of the total, but that kind of kills the dynamic aspect. I wish I knew what was creating this hidden worksheet, and I can't seem to even unhide it.

Good call, I can see it in the VBA editor. Anyway I can see the contents of it?

It was completely blank and causing so many problems. Thank you so much for your help JosephP!

It's solved though, so they did