VBA, If Cells Blank End Sub

[MrMaker]

Hello everyone,

A simple one no doubt........I have inherited the below code and would like to amend to include a statement at the beginning to echo the following: IF CELL Z1 ON SHEET 6 IS BLANK THEN END SUB, OTHERWISE DO ALL THE BELOW.

Can anyone advise please?

Thanks very much in advance

Private Sub Workbook_Open()

Dim Ws As Worksheet

Dim Crit As String

Application.ScreenUpdating = False

Crit = Sheets("SHEET1").Range("A1").Value

For Each Ws In Worksheets

Select Case Ws.Name

Case "SHEET2", "SHEET3"

Ws.UsedRange.AutoFilter 1, "<>" & Crit

Ws.AutoFilter.Range.Offset(1).EntireRow.Delete

Ws.AutoFilterMode = False

Ws.Range("A2:A" & Ws.Rows.Count).RowHeight = 25

Ws.Activate

Ws.Cells.Copy

Ws.Range("A1").PasteSpecial Paste:=xlValues

Ws.Range("A1").Select

Application.CutCopyMode = False

End Select

Select Case Ws.Name

Case "SHEET4"

Ws.Range("A1").Select

End Select

Next Ws

With Sheets("SHEET1")

.Range("A1").Value = Crit

.Rows.RowHeight = 36

.Visible = xlVeryHidden

Worksheets("Summary").Activate

ThisWorkbook.Protect Password:="123", Structure:=True, Windows:=True

End With

End Sub

 

[DanteAmor]

Verify that the sheet name is correct "SHEET6".
Add the following line to the macro, it is highlighted in blue.

Private Sub Workbook_Open()
Dim Ws As Worksheet
Dim Crit As String
Application.ScreenUpdating = False

If Sheets("SHEET6").Range("Z1").Value = "" Then Exit Sub

Crit = Sheets("SHEET1").Range("A1").Value
For Each Ws In Worksheets
Select Case Ws.Name
Case "SHEET2", "SHEET3"
Ws.UsedRange.AutoFilter 1, "<>" & Crit
Ws.AutoFilter.Range.Offset(1).EntireRow.Delete
Ws.AutoFilterMode = False
Ws.Range("A2:A" & Ws.Rows.Count).RowHeight = 25
Ws.Activate
Ws.Cells.Copy
Ws.Range("A1").PasteSpecial Paste:=xlValues
Ws.Range("A1").Select
Application.CutCopyMode = False
End Select
Select Case Ws.Name
Case "SHEET4"
Ws.Range("A1").Select
End Select
Next Ws
With Sheets("SHEET1")
.Range("A1").Value = Crit
.Rows.RowHeight = 36
.Visible = xlVeryHidden
Worksheets("Summary").Activate
ThisWorkbook.Protect Password:="123", Structure:=True, Windows:=True
End With
End Sub

 

[MrMaker]

Easy when you know how
Thanks very much for your really quick help!
All the best