VBA - Looping on all worksheets on a workbook

Asked Apr 06 '23 at 17:10

Active Apr 06 '23 at 17:10

Viewed 15 times

Sub Ticker2()

  
    Dim Ticker As String

    Dim Brand_Total As Double
    Brand_Total = 0

    Dim Summary_Table_Row As Integer
    Summary_Table_Row = 2
    
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets

 
    For i = 2 To 760000

    If Cells(i + 1, 1).Value <> Cells(i, 1).Value Then
      
      Ticker = Cells(i, 1).Value
      Brand_Total = Brand_Total + Cells(i, 7).Value
      Range("I" & Summary_Table_Row).Value = Ticker
      Range("L" & Summary_Table_Row).Value = Brand_Total
      Summary_Table_Row = Summary_Table_Row + 1
      Brand_Total = 0
    
    Else

      Brand_Total = Brand_Total + Cells(i, 7).Value
      
    End If

    Next i
    
    Next ws
  
End Sub

I am beginner on VBA and I am struggling a bit on looping this code into different worksheets, I tried the Loop For Each but is not working, anyone can identified what I am missing?

edited Apr 06 '23 at 17:10

asked Apr 06 '23 at 17:10

Tadeo Salinas

Put `ws.` before each instance of `Cells` and `Range` – BigBen Apr 06 '23 at 17:11
760000 rows on several worksheets will take quite a while to be processed. If you will run it often, you may prefer to save the whole range in an array and then work with the array. – Stringeater Apr 06 '23 at 17:19
@Stringeater yes, good idea, just did it, thanks! – Tadeo Salinas Apr 06 '23 at 17:55

VBA - Looping on all worksheets on a workbook

0 Answers0