Running Automaticaly vba Macro in all worksheets when open xlsm file

Question

Why is not working automatically this vba macro in all worksheets?

Private Sub Workbook_Open()
Dim cRow As Long
Dim rRow As Range
Dim LastRow As Long
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    With ws
        LastRow = [A65000].End(xlUp).Row
        For cRow = 1 To LastRow
            If Cells(cRow, 15) = "OnGoing" Then
                Rows(cRow).Font.Bold = True
                Rows(cRow).Font.Color = RGB(156, 204, 0)
            End If
            If Cells(cRow, 15) = "Modified" Then
                Rows(cRow).Font.Bold = True
            End If
        Next cRow
        Columns("A:O").EntireColumn.AutoFit
    End With
Next ws
End Sub

What am I doing wrong on it?

this line `LastRow = [A65000].End(xlUp).Row` would be more resilient by being written as `ws.cells(ws.cells.rows.count, 1).End(xlUp).Row` — JohnnieL, Feb 08 '21 at 09:40
You have used 'With ws' which indicates that you want to use ws as the qualifying reference. However, you did not put a '.' in front of your Cell , Rows and Columns methods. As a consequence your Cells ,Rows and Column methods will only ever refer to the ActiveWorksheet. You should change to using .Cell ,.Rows and .Columns (i.e. ws.Rows , ws.Cells and ws.Columns) — freeflow, Feb 08 '21 at 09:50
Two links for you [Fully qualify your cells](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) as @freeflow mentioned and [Finding the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) — Siddharth Rout, Feb 08 '21 at 09:53

JohnnieL · Accepted Answer · 2021-02-08T10:04:13.030

Create a public sub called auto_open to run code on opening an xlsm workbook - what youve constructed seems consistent with MS documentation, but auto_open in a project module always works with no problems ..

https://support.microsoft.com/en-us/office/automatically-run-a-macro-when-opening-a-workbook-1e55959b-e077-4c88-a696-c3017600db44

If the module is being triggered (put a msgbox in to verify) then it could be because you are not using fully qualified range/cell names, so you need . in front of cells and rows

            If .Cells(cRow, 15) = "OnGoing" Then
                .Rows(cRow).Font.Bold = True
                .Rows(cRow).Font.Color = RGB(156, 204, 0)
            End If
            If .Cells(cRow, 15) = "Modified" Then
                .Rows(cRow).Font.Bold = True
            End If

score 0 · Answer 2 · answered Feb 08 '21 at 10:06

@freeflow did you mean this one?

Private Sub Workbook_Open()
Dim cRow As Long
Dim rRow As Range
Dim LastRow As Long
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    With ws
        LastRow = ws.Cells(ws.Cells.Rows.Count, 1).End(xlUp).Row
        For cRow = 1 To LastRow
            If ws.Cells(cRow, 15) = "OnGoing" Then
                ws.Rows(cRow).Font.Bold = True
                ws.Rows(cRow).Font.Color = RGB(156, 204, 0)
            End If
            If ws.Cells(cRow, 15) = "Modified" Then
                ws.Rows(cRow).Font.Bold = True
            End If
        Next cRow
        ws.Columns("A:O").EntireColumn.AutoFit
    End With
Next ws
End Sub

it is working on the activeworkingsheet yet.

Already working properly. Thanks to all. – Fernando Rodríguez Feb 08 '21 at 10:09 — Fernando Rodríguez, Feb 08 '21 at 10:09

score -1 · Answer 3 · answered Feb 08 '21 at 09:47

You can try activating the sheet before doing the calculation, so your code should look like

Private Sub Workbook_Open()
Dim cRow As Long
Dim rRow As Range
Dim LastRow As Long
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    With ws
        .activate
        LastRow = [A65000].End(xlUp).Row
        For cRow = 1 To LastRow
            If Cells(cRow, 15) = "OnGoing" Then
                Rows(cRow).Font.Bold = True
                Rows(cRow).Font.Color = RGB(156, 204, 0)
            End If
            If Cells(cRow, 15) = "Modified" Then
                Rows(cRow).Font.Bold = True
            End If
        Next cRow
        Columns("A:O").EntireColumn.AutoFit
    End With
Next ws
End Sub

That is a very bad way to resolve the issue. The correct solution is to prefix with '.' as I show in my comment to the original post. — freeflow, Feb 08 '21 at 09:50
I agree with @freeflow: You may want to see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Siddharth Rout, Feb 08 '21 at 09:51

Running Automaticaly vba Macro in all worksheets when open xlsm file

3 Answers3