How to apply a VBA code to every page in a workbook? Mine does part of the code to every page, but only applies entire code to last page

Question

I'm writing a VBA code for a workbook with 7 pages. Each page has stock data in the same layout. Each stock is identified with a ticker, and has data for opening, closing, high, low, and volume for each day of the year.

I want a code that will create a new column for the ticker, and beside that columns for Yearly Change, Percent Change, and Total Volume.

I want to be able to run the macro once and have it loop through every page for the workbook. It will label the ticker and calculate the Total Stock Volume on each page, but it only calculates the Yearly Change and Percent Change for the last page.

This is what should happen on each page

This is what all but the last page look like

As you can see from the images, it's doing part of the code on each page, but only the last page gets the entire code applied thereto.

Could anyone tell me what's going on or give me a hint? The macro is definitely running through each page since columns I and L are getting done, but columns J and K are only done on the last page.

Here is the code I'm using: Sub Stocks()

    'This creates a "worksheet loop" that will automatically cycle through each page of the workbook and apply the macro thereto.
    Dim sheets As Worksheet
    For Each sheets In ThisWorkbook.Worksheets
        sheets.Activate

        'The proceeding lines create the column headings, as well as the headings horizontal headings used to show the greatest and least perchange change as well as the greatest total volume.
        Range("I1").Value = "Ticker"
        Range("J1").Value = "Yearly Change"
        Range("K1").Value = "Percent Change"
        Range("L1").Value = "Total Stock Volume"

        Range("P1").Value = "Ticker"
        Range("Q1").Value = "Value"
        Range("O2").Value = "Greatest % Increase"
        Range("O3").Value = "Greatest % Decrease"
        Range("O4").Value = "Greatest Total Volume"
        Range("O5").Value = "Least Total Volume"

        'This creates a variable which will identify and label the stock ticker.
        Dim stock_ticker As String

        'This creates a variable which will hold the total stock volume.
        Dim stock_volume As Double
        stock_volume = 0

        'This variable is used to input the ticker value in the correct cell in column I.  The ticker changes at a faster rate in column I than in column A.  This variable is therefore used to adjust the rate tickers are copied over from column A to column I.
        Dim j As Integer
        j = 2

        'This loop checks to see if the value in cell 'Ax" is equal to "Ay", where x and y are integers, and y=x+1.  If they are equal, Excel will recognize the tickers as being the same, and add the stock volume in the xth row to an accumlative total stock volume.  If Ax does not equal Ay, Excel will recognize that the ticker just changed.  When this happens, Excel will will add the last stock volume for the current ticker to the accumlative total stock volume; then it will identify what the current ticker is and insert this into column I, insert the total stock volume for that ticker into column L, then reset the total stock volume back to 0, then repeat the process.
        For i = 2 To 43398
            If Cells(i, 1).Value = Cells(i + 1, 1).Value Then
                stock_volume = stock_volume + Cells(i, 7).Value
            ElseIf Cells(i, 1).Value <> Cells(i + 1, 1).Value Then
                stock_volume = stock_volume + Cells(i, 7).Value
                Cells(j, 9).Value = Cells(i, 1).Value
                Cells(j, 12).Value = stock_volume
                j = j + 1
                stock_volume = 0
            End If
        Next i

        Dim stock_year_start As Double
        Dim stock_year_end As Double
        Dim stock_year_change As Double
        Dim stock_percent_change
        Dim k As Integer
        k = 2

        For i = 2 To 43398
            If Right(Cells(i, 2), 4) = "0101" Then
                stock_year_start = Cells(i, 3)
            ElseIf Right(Cells(i, 2), 4) = "1231" Then
                stock_year_end = Cells(i, 6)
                stock_year_change = stock_year_end - stock_year_start
                stock_percent_change = stock_year_change / stock_year_start
                Cells(k, 10).Value = stock_year_change
                If Cells(k, 10).Value > 0 Then
                    Cells(k, 10).Interior.ColorIndex = 4
                ElseIf Cells(k, 10).Value < 0 Then
                    Cells(k, 10).Interior.ColorIndex = 3
                End If
                Cells(k, 11).Value = stock_percent_change
                k = k + 1
            End If
        Next i

        Range("K1").EntireColumn.NumberFormat = "0.00%"

        'The proceeding lines automatically resize the cells created throughout the program to fit the content therein.
        Dim sheet_name As String
        sheet_name = ActiveSheet.Name
        Worksheets(sheet_name).Columns("I:L").AutoFit
        Worksheets(sheet_name).Columns("O:Q").AutoFit

    'This cycles to the next page in the workbook and repeats all the code hitherto.
    Next sheets

End Sub

Unrelated but use With Sheets ...... End With so you can remove the .Activate for faster code running. See info [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) And if commented out code is not important in relation to the question can you [edit] it out. — QHarr, Aug 31 '18 at 21:45
you are going to need to step through with F8 and see where the code deviates from what you expected. You only need to do this for one failing sheet. It seems likelythat it is a logic problem. — QHarr, Sep 01 '18 at 05:49

urdearboy · Answer 1 · 2018-08-31T22:57:32.897

I would avoid dimming variables with object names, so consider changing the Sheets in Dim sheets as Worksheet to ws.

To loop through sheets and apply the same logic you need to do something like this:

Dim ws as Worksheet

For Each ws in Worksheets
    'Your code goes here with all objects referring to current ws like so:
     ws.Range(....
     ws.Cells(....
Next ws

Do not activate the sheet. Instead, qualify every object (Range, Cells, etc) with the variable ws. I would use a find & replace and swap Range with ws.Range and then swap Cells with ws.Cells. It would look something like this in your code.

Sub Stocks()

Dim stock_ticker As String, stock_volume As Double, j As Integer

Dim ws As Worksheet
For Each ws In Worksheets

ws.Range("I1").Value = "Ticker"
ws.Range("J1").Value = "Yearly Change"
ws.Range("K1").Value = "Percent Change"
ws.Range("L1").Value = "Total Stock Volume"
ws.Range("P1").Value = "Ticker"
ws.Range("Q1").Value = "Value"
ws.Range("O2").Value = "Greatest % Increase"
ws.Range("O3").Value = "Greatest % Decrease"
ws.Range("O4").Value = "Greatest Total Volume"
ws.Range("O5").Value = "Least Total Volume"

stock_volume = 0

Dim j As Integer
j = 2

For i = 2 To 43398
    If ws.Cells(i, 1).Value = ws.Cells(i + 1, 1).Value Then
        stock_volume = stock_volume + ws.Cells(i, 7).Value
    ElseIf ws.Cells(i, 1).Value <> Cells(i + 1, 1).Value Then
        stock_volume = stock_volume + ws.Cells(i, 7).Value
        ws.Cells(j, 9).Value = ws.Cells(i, 1).Value
        ws.Cells(j, 12).Value = stock_volume
        j = j + 1
        stock_volume = 0
    End If
Next i

I made these changes and it didn't do anything. Each page gets the ticker and the total stock volume, but only the last page gets the yearly and percent change, as shown in the pictures provided. — Tyler, Sep 01 '18 at 03:20
If it only did stuff on the last page, then I'd feel better, but seeing that it produces two columns on each page tells me that it is looping through all the pages. Everything is inside the "workpage-loop," and no matter which page I run it on, it's only the last page that gets completely done. I've looked over it, taken a break and come back, and nothing looks wrong to me. Could you think of any reason why it's the last page that gets done regardless of which page I run the macro on? — Tyler, Sep 01 '18 at 03:47
Check the format of the sheets. Make sure they are all number formats. I can look more in a hour or something — urdearboy, Sep 01 '18 at 03:55
Try stepping though the code with F8 and check the values of your variables — urdearboy, Sep 01 '18 at 04:07

score 0 · Answer 2 · answered Sep 01 '18 at 21:40

I found the problem. Most stocks end on 12/30, and I was only checking for 12/31. I have rewritten the second loop as:

    For i = 2 To 43398
        If Right(workbook_sheet.Cells(i, 2), 4) = "0101" Then
            stock_year_start = workbook_sheet.Cells(i, 3)
        ElseIf Right(workbook_sheet.Cells(i, 2), 4) = "1231" Then
            stock_year_end = workbook_sheet.Cells(i, 6)
            stock_year_change = stock_year_end - stock_year_start
            stock_percent_change = stock_year_change / stock_year_start
            workbook_sheet.Cells(k, 10).Value = stock_year_change
        ElseIf Right(workbook_sheet.Cells(i, 2), 4) = "1230" Then
            stock_year_end = workbook_sheet.Cells(i, 6)
            stock_year_change = stock_year_end - stock_year_start
            stock_percent_change = stock_year_change / stock_year_start
            workbook_sheet.Cells(k, 10).Value = stock_year_change
        If workbook_sheet.Cells(k, 10).Value > 0 Then
                workbook_sheet.Cells(k, 10).Interior.ColorIndex = 4
        ElseIf workbook_sheet.Cells(k, 10).Value < 0 Then
                workbook_sheet.Cells(k, 10).Interior.ColorIndex = 3
        End If
        workbook_sheet.Cells(k, 11).Value = stock_percent_change
        k = k + 1
        End If
    Next i

I'm thinking of a more elegant way of finding the year-end stock value. The 43398 is also a temporary value, as each page has a different number of stocks to check for and I'm still looking for a while to find the number of rows in each field.

I'll leave this here in case anyone wants to comment.

How to apply a VBA code to every page in a workbook? Mine does part of the code to every page, but only applies entire code to last page

2 Answers2