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.
Is there a way to do this?
Thank you,
Rubi
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.
Code:
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