CODEWITHSUNDEEP
vba
AAli
AAli

Reputation: 13

Running a macro automatically in one sheet when workbook is opened

I'm trying to run a macro automatically when a workbook is opened. I was using the privatesub command in ThisWorkbook tab.

However it seems when I shut the Excel file and open it again, the macro has run on another sheet as well resulting in a circular references error. How do I solve this so it only runs on one sheet ("Cover Sheet"). Would placing the macro in he actual sheet module work?

Private Sub Workbook_Open()    
With Sheets("Cover Sheet")
With Range("B21")
        .Formula = "=COUNTIFS('Design Risk Scoring Sheet'!$AN$12:$AN$" & Sheets("Design Risk Scoring Sheet").Cells(Rows.count, "AN").End(xlUp).Row & ",""<""&B20, 'Design Risk Scoring Sheet'!$B$12:$B$" & Sheets("Design Risk Scoring Sheet").Cells(Rows.count, "AN").End(xlUp).Row & ", """" )"
        .AutoFill Destination:=Range("B21:AF21"), Type:=xlFillDefault
End With

With Range("B22")
        .Formula = "=COUNTIFS('Design Risk Scoring Sheet'!$BF$12:$BF$" & Sheets("Design Risk Scoring Sheet").Cells(Rows.count, "AN").End(xlUp).Row & ",""<""&B20, 'Design Risk Scoring Sheet'!$B$12:$B$" & Sheets("Design Risk Scoring Sheet").Cells(Rows.count, "AN").End(xlUp).Row & ", """" )"
        .AutoFill Destination:=Range("B22:AF22"), Type:=xlFillDefault
End With
End With

Upvotes: 1

Views: 237

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19077

You only missed dots twice but important dots. Your second and third With...End With object reference should start with dots in this way:

With .Range("B21")
    '...your code here
End With

With .Range("B22")
    '...your code here
End with

Upvotes: 3

Related Questions