VBA loop of multiple sheets in a worksheet

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


2 Answers
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


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:

ws.Activate

ws

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


Now, the following perplexes me for a few reasons:

Set sht = Worksheet


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.

ws == sht

ws

sht

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.

sht

Option Explicit

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:

MsgBox ws.Name

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.