VBA to auto hide worksheets

[Pierce Quality]

Hello All,
I am attempting to configure a code to auto hide all worksheets in a workbook that do not have a value in cell A2, but leaves the sheets that do have a value in cell A2 visible. I want this to happen when the user cliucks the save button.
Here is what I have so far:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim ws As Worksheet  ' Declares variable
 
For Each ws In ThisWorkbook.Worksheets  'Start looping through all worksheets
 
If Range("A2") = "" Then   Check if each worksheet range is empty
 ws.Visible = False

End If
 
Next ws  'Loop to next worksheet

End Sub

When I place this code in the workbook module and click save, nothing will happen to to any sheets when I have an active sheet that does have a value in A2, but when I have a visible sheet that does not have a value in A2, the code closes all worksheets in order up to the worksheet that is visible.
Any help to point me in the right direction would be greatly appreciated!
Thanks!

[JOHN H. DAVIS]

Try your code with ws.activate

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet  ' Declares variable
 
For Each ws In ActiveWorkbook.Worksheets  'Start looping through all worksheets
ws.Activate
If Range("A2").Value = "" Then   'Check if each worksheet range is empty
 ws.Visible = False

End If
 
Next ws  'Loop to next worksheet
End Sub

[Pierce Quality]

Try your code with ws.activate

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet  ' Declares variable
 
For Each ws In ActiveWorkbook.Worksheets  'Start looping through all worksheets
ws.Activate
If Range("A2").Value = "" Then   'Check if each worksheet range is empty
 ws.Visible = False

End If
 
Next ws  'Loop to next worksheet
End Sub

Thanks John, that worked great, appreciate it!

Marking this as solved.

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback.