VBA loop of multiple sheets in a worksheet

Question

I have a VBA macro that is working correctly to loop through a column until the last row. What I need is it to run through multiple tabs or sheets in the excel file. Here's what i have so far:

Sub Stocks()

    Dim Ticker As String

    Dim Total_Stock_Volume As Double
    Total_Stock_Volume = 0

    Dim Summary_Table_Row As Integer
    Summary_Table_Row = 2
    Dim lastRow As Long

    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Activate
    Next ws

    Set sht = Worksheet
    lastRow = ws.Cells(sht.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow

        Total_Stock_Volume = Total_Stock_Volume + ws.Cells(i, 7).Value

        If ws.Cells(i + 1, 1).Value <> ws.Cells(i, 1).Value Then

            Ticker = ws.Cells(i, 1).Value

            ws.Range("J" & Summary_Table_Row).Value = Ticker
            ws.Range("K" & Summary_Table_Row).Value = Total_Stock_Volume

            Summary_Table_Row = Summary_Table_Row + 1

            Total_Stock_Volume = 0

        End If
    Next i

End Sub

Answer 1

This following code is what I would call a useless dumpster fire.

Dim ws As Worksheet
For Each ws In Worksheets
    ws.Activate
Next ws

Set sht = Worksheet
lastRow = ws.Cells(sht.Rows.Count, "A").End(xlUp).Row

The reason I call it that is because using ws.Activate is the equivalent of clicking the worksheet tab (hence useless in this case) and your ws variable is referencing only the last worksheet, which, if your intent is to set a reference to the last worksheet, can be done by using:

Set ws = Excel.Application.ThisWorkbook.Worksheets(YourLastWorksheetName)

Now, the following perplexes me for a few reasons:

Set sht = Worksheet

• Which worksheet?
• Which workbook are you referencing the worksheet from?
• I didn't even know one could reference anything from Worksheet

Just to simplify things, I'm assuming that ws == sht because I'm assuming the goal was to test if you could work with 1 worksheet before you asked about iterating through multiple worksheets, so you can simplify this by setting 1 reference to the 1 worksheet you're working with - I previously did this with ws - instead of setting another reference to whatever worksheet sht is referencing.

Also, sht is not explicitly declared, so I would suggest writing Option Explicit in the very first line of every Module or Class Module you write because, at compile time, it will show you which variables you haven't explicitly declared, which will notify you if you have mispelled any variables by accident.

This bit is what I would call the dumpster fire:

lastRow = ws.Cells(sht.Rows.Count, "A").End(xlUp).Row

Because I'm assuming your goal was to work with 1 worksheet, this line of code looks like you're working with 2 poorly-referenced worksheets, which, somewhere down the line, may not work as you intend.

Here's how I would change your code:

Edit: This code compiled in Excel so it should work, but you'll have to test it for runtime errors.

'i like this because i know when i have
'incorrectly named a variable // otherwise
'VBA will just create the incorrectly-named variable
'and set its type to Variant
Option Explicit

'i like to explicitly state whether a sub or function
'will be able to be called from outsite the module (public)
'or if i want it to only be called from within the module (private)
Public Sub Stocks()
    'i prefer to keep all my 'Dim' statements in 1 block so they're
    'easier to find later when i need to change something
    Dim Total_Stock_Volume As Double
    Dim Summary_Table_Row As Integer
    Dim lastRow As Long
    Dim ws As Worksheet
    Dim j As Long
    Dim i As Long
    Dim Ticker As String

    'i prefer to clump my like assignments together in a block
    Summary_Table_Row = 2
    Total_Stock_Volume = 0

    'iterate through the collection of worksheets in your workbook
    For j = 1 To Excel.Application.ThisWorkbook.Worksheets.Count
        'set a reference to a worksheet // this will go through
        'the different worksheets in the workbook as the loop
        'progresses
        Set ws = Excel.Application.ThisWorkbook.Worksheets(j)

        With ws
            'this is a better way to get the last column in a worksheet
            lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        End With

        For i = 2 To lastRow
            'i prefer to explicitly cast anything i get from a cell to the type
            'i intend to use because .Value returns a Variant type by default
            Total_Stock_Volume = Total_Stock_Volume + CDbl(ws.Cells(i, 7).Value)

            If ws.Cells(i + 1, 1).Value <> ws.Cells(i, 1).Value Then
                'me explicitly casting the value of the cell to a string
                Ticker = CStr(ws.Cells(i, 1).Value)

                'i like with statements because it looks nicer to me.
                'i'm sure there's a better reason to use these, but that's
                'my reason!
                With ws
                    .Range("J" & Summary_Table_Row).Value = Ticker
                    .Range("K" & Summary_Table_Row).Value = Total_Stock_Volume
                End With
                Summary_Table_Row = Summary_Table_Row + 1

                'i'm unsure about the intention with this, so i'll leave it alone
                Total_Stock_Volume = 0
            End If
        Next i

    Next
End Sub

Hope it helps!

Edit: Added screenshots of what the output from the above code looks like when I run it. As I said in the comments, without further information about what you want this code to do, I can't really do anything else to help.

Sheet1:

Sheet2:

Sheet3:

Answer 2

here generic syntax for you

 dim wb as workbook
 dim sheet as worksheet
 set wb = ThisWorkbook
 for each sheet in wb.WorkSheets
      'processing logic
 next sheet