Run Macro on Multiple Excel Worksheets

Question

I am seeking help to run a single macro on multiple excel worksheets (There are several previous related questions but I didn't think/know if they applied to my issue). Each sheet has a different ticker. I am trying to pull historical stock prices for a different stock on each excel worksheet. As you will note from the VBA code, the ticker is located in K1 for each worksheet.

Right now, I am able to run the same macro on multiple worksheets using the code below. However, the macro runs using the same ticker for all worksheets. For example, the ticker in the first worksheet is "WMT" and the macro pulls the historical stock price on all the worksheets using "WMT" instead of the unique ticker for each worksheet. Does anyone know how to make the macro run on each worksheet so that the macro uses the unique ticker located on each worksheet?

    Sub Data_Get()
'
' Data_Get Macro
'
Dim ticker As String, sday, smonth, syear, eday, emonth, eyear As Long, ws As Worksheet


ticker = Range("k1")
sday = Day(Range("k2"))
smonth = Month(Range("k2")) - 1
syear = Year(Range("k2"))
eday = Day(Range("k3"))
emonth = Month(Range("k3")) - 1
eyear = Year(Range("k3"))

'
For Each ws In Sheets
ws.Activate
Columns("A:G").ClearContents

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;http://real-chart.finance.yahoo.com/table.csv?s=" & ticker & "&d=" & emonth & "&e=" & eday & "&f=" & eyear & " &g=w&a=" & smonth & "&b=" & sday & "&c=" & syear & "&ignore=.csv" _
        , Destination:=Range("$A$1"))
        .Name = "Datatable"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(5, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

Next ws

End Sub

Answer 1

Here is how you can loop all worksheets in the workbook and call your sub.

    Dim iIndex as integer
    Dim ws As Excel.Worksheet

    For iIndex = 1 To ActiveWorkbook.Worksheets.count
        Set ws = Worksheets(iIndex)
        ws.Activate

        Data_Get

    Next iIndex

Answer 2

You have the assignment

ticker = Range("k1")

before you enter your main loop. If K1 has a different value on each worksheet and you want the code to refer to that -- you need to move that line to within the main loop (say to right after ws.Activate). Similar remarks hold for the other assignments that reference specific cells. If you run the assignment before the loop then the loop won't change their values.

Answer 3

Often you will need to run your macro on all the sheets but one. For example you collect data from all sheets into the sheet "Report":

Dim Sh As Excel.Worksheet
For Each Sh in ActiveWorkbook.Worksheets
    If Sh.Name <> "Report" Then Data_Get Sh
Next Sh

This will require changing your original macro and make it independent from the active sheet:

Sub Data_Get(Sh As Worksheet)
[...]
ticker = Sh.Range("k1")
sday = Day(Sh.Range("k2"))