How can I run multiple subs from a single page?

[Rubicon9]

Hello all,
I'm trying to run all my worksheets from the Sheet1 (Report) with using just a button.


Currently I have to go to each sheet and run them one by one. I'd like to have this automated.


here is my code that I have in all 72 worksheets (I have this type of format "But different Ranges" in all 72 pages).
WorkSheet1 name is "Report" and does not have any code in it.

Sub ADOExcelSQLServer()
     
    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
     
    Server_Name = "xxxx"
    Database_Name = "xxxx"
    User_ID = "xxxx"
    Password = "*xxxx"
    ' Pulling Data for ?
    SQLStr = "Sxxxx"
    
     
    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"
     
    rs.Open SQLStr, Cn, adOpenStatic
     ' Dump to spreadsheet
    With Worksheets("Kansas City").Range("a3:z500") ' Enter your sheet name and range here
        .ClearContents
        .CopyFromRecordset rs
    End With
     ' clean up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
    
    Dim ws As Worksheet
    For Each ws In Worksheets
        With ws.Range("A" & Rows.Count).End(xlUp).Offset(2, 7)
            .NumberFormat = "General"
            .Value = .Row - 4
            Application.ScreenUpdating = False
            Worksheets("Report").Range("D134").Value = Worksheets("Kansas City").Range("H9").Value
            Application.ScreenUpdating = True


        End With
    Next
   
 End Sub

Is there a way to do this?

Thank you,
Rubi

 

[netuser]

You have to put your code at Modules and not in sheet, in sheet then you can call the Sub