Renaming Sheets and searching queries

phimutau

New Member
Joined
Aug 29, 2021
Messages
19
Office Version
  1. 2010
Platform
  1. Windows
1630281067105.png


I would like to rename 228 sheets with the teams listed in cell b2:b229. The sheets already exist and are based off a template.
The template has two cells that are web queries. ("Template" i25 for column D and "Template" ac25 for Column E)
These web queries are listed in columns D and E and are associated with each team.

1630281771338.png


Can someone help me with renaming these 228 sheets and searching their respective queries? Doing this manually without VBA would take me a month.

Once inside the query I select "disable date recognition" so that my data does not show up as dates. Will this cause problems?

Thanks for your help.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
My web query is created. I have to do this twice per sheet to get the player ratings and then the player formations.
Aaha! I was on the wrong track because I thought the web queries on the 228 sheets already existed and you just wanted to rename the sheets. I see now that these 228 x 2 web queries don't actually exist and you want to create them on the sheets, renamed according to column B of the "List" sheet and using the URLs in columns D and E. But how do you know which pair of web queries should go on which sheet?

I suggest a different approach, however the 2 macros I've posted show that there are no web queries on the "Template" sheet. First, create a new workbook with only the "List" and "Template" sheets. For testing, the data on "List" can be just a few rows, not all 228 rows. On the "Template" sheet, you must create the 2 web queries in I25 and AC25 for any team.

Put this code in a regular module in the new workbook and run the Copy_Template_Modify_Web_Queries macro. For each row on "List" it adds a new sheet by copying the "Template" sheet and modifying the 2 web queries to use the URLs in columns D and E. After running the macro you can Refresh All to retrieve the web data and update all the sheets, or delete this apostrophe 'qt.Refresh to have the macro refresh the web queries as it creates them.

VBA Code:
Public Sub Copy_Template_Modify_Web_Queries()

    Dim TemplateSheet As Worksheet
    Dim NewTemplateSheet As Worksheet
    Dim r As Long
    Dim qt As QueryTable
    
    Set TemplateSheet = ThisWorkbook.Worksheets("Template")
    
    With ThisWorkbook.Worksheets("List")
    
        For r = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row
        
            'Add new sheet by copying Template sheet
            
            TemplateSheet.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
            Set NewTemplateSheet = ActiveSheet
            
            'Rename new sheet as List column B cell
            
            NewTemplateSheet.Name = .Cells(r, "B").Value
            
            'Modify web query connection URLs according to List column D and E cells
            
            For Each qt In NewTemplateSheet.QueryTables
                If InStr(qt.Connection, "PlayerRatings") Then
                    qt.Connection = "URL;" & .Cells(r, "D").Value
                ElseIf InStr(qt.Connection, "Formations") Then
                    qt.Connection = "URL;" & .Cells(r, "E").Value
                End If
                Debug.Print NewTemplateSheet.Name, qt.Destination.Address, qt.Connection
                'qt.Refresh BackgroundQuery:=True  'or False
            Next
        
        Next
        
        .Activate
        
    End With
        
    MsgBox "Done"
    
End Sub
 
Upvote 0
Solution
OK, it worked sort of. I pulled the "List" sheet and the "Template" sheet into a new workbook and I deleted the list of schools down to 10, instead of 228. It made all new sheets from the list and added the queries. I deleted the apostrophe from the qt so it would update as it made them. I did get the following messages, but it did work and I can clean it up.

1631027364626.png


1631027397453.png
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top