VBA to change sheet names from list, entering sheet name into sheet, then pausing

Disclaimer: I have very little VBA knowledge

I currently have a master sheet (Sheet 1) containing about 200 codes (C2:C201), with 200 individual sheets in the workbook I want to be renamed, edited individually, and have their separate web queries updated in each sheet.

Ideally what I would like to happen is to have VBA rename sheet2 from the list (it would be from c2, "AAA"), then within sheet2 (now renamed AAA) I would like the new name of the sheet to be entered in cell H1 (or just have the formula below entered, which successfully retrieves the sheet name and enters it in the cell). I have web queries in each of the sheets synced up so that when H1 is changed, they automatically update from the web, so I would then need the vba code to pause for 5-10 seconds, while the web queries update, and once the time has elapsed and the sheet updated, move on and do the same thing for sheet3, sheet4, sheet5, etc. pausing each time to let each sheet update from their individual web queries after their names have been changed.

How I currently do it is use a script I found somewhere on the internet to rename all the sheets, then select about 20 or so sheets and copy my formula ("=MID(CELL("filename",A2),FIND("]",CELL("filename",A2))+1,255") into the sheets at the same time, and then let the web queries on those 20 sheets update, then move onto the next 20 sheets, etc. The reason I do this is because if I select and paste the formula into all 200 sheets, or use the "Refresh All" button, excel seems to hang after 5 minutes or so. I've done some searching around and this seems to be because Internet Explorer (which runs the web queries) has a timeout of 5 minutes, which can be extended if you edit the appropriate registry key. However, now matter what I change the timeout to it always seems to still timeout at the same time, after 5 minutes has elapsed.

The reason why this whole process seems so patched and mismatched is because I've been slowly changing and upgrading it over the years, but I do not have the skill and knowledge to adapt the entire process, so I fill in little bits here and there where I can. So, whilst what I currently have is better than nothing, if someone also has suggestions on perhaps how this could be performed more easily I'd be willing to listen.

I am grateful for any advice or help, or any information which leads me in the right direction.

Thanks in advance

Heres' another one. It refreshes the web queries. The argument BackgroundQuery:=False will automatically pause the macro until each web query is done refreshing.

Please Login or Register  to view this content.

Both of these work perfectly.

A lot of thanks to both of you!

One last question, when a sheet snags for whatever reason, I noticed that both macros have a tendency to just freeze and get caught on the error while it is just waiting patiently for the web queries to update. Is there something that could be added that makes the script move on after 20 seconds or something, cancelling the update of the sheet it was stuck on, as I will often let this run when I'm away from the computer so I won't be able to manage it myself.

Hi

It seems that we may be doing the same thing with one exception that I use 1 instead of 200 Worksheets. I attach different Web-Query Tables to different cells in column A of same sheet and added a small function to add formulas to parse data off that cell. How often do you refresh each sheet?

One thing, I can add to your knowledge-kitty is that there is method

Please Login or Register  to view this content.

that does exactly what it says.

Also, enclosing sub in Application.ScreenUpdating = False and Application.ScreenUpdating = True will help speed up things....