Macro to go through each sheet and execute code if statement

[Will85]

I have a tab in my workbook called "Macro" in which I have hard keyed the names of specific sheets within my workbook. My current code uses this "Macro" tab to "know" on which tabs to execute my code, my code could be hidding blank rows, showing hidden columns, printing tabs to .pdf and saving on sharepoint, etc.

Instead of using a "Macro" tab, I would like to have my macro go through each worksheet and check if a value is present in a certain cell. For example, go to sheet 1 if cell A1 = "Publish" then execute code, then go to sheet 2 and repeat until its gone through all sheets in the workbook. If cell A1 doesn't equal publish then go to the next sheet.

I want to empower my users to decide which sheets to include, and I also dont want to have to manage my old "Macro" tab anymore.

Bonus points would be for it to only go through visible sheets (I have many hidden sheets that would never be included in this process)

 

[JoeMo]

Dim Sht As Worksheet

Sub Will85()
For Each Sht In ActiveWorkbook.Worksheets
    If Sht.Visible And Sht.Range("A1").Value = "Publish" Then
        Call Hello  'substitute your macro name for Hello
    End If
Next Sht
End Sub
Sub Hello()    'put the macro you want to run on sheets that meet your criteria here
MsgBox "HELLO from sheet " & Sht.Name
End Sub

 

[Will85]

Thank you in advance, I will give it a go and report back.

 

[Will85]

Dim Sht As Worksheet

Sub Will85()
For Each Sht In ActiveWorkbook.Worksheets
    If Sht.Visible And Sht.Range("A1").Value = "Publish" Then
        Call Hello  'substitute your macro name for Hello
    End If
Next Sht
End Sub
Sub Hello()    'put the macro you want to run on sheets that meet your criteria here
MsgBox "HELLO from sheet " & Sht.Name
End Sub

It only works for the sheet that I am on, it does not go through each subsequent sheet in my workbook.

Was I suppose to put the "Dim Sht As Worksheet" in a special location? When I include it in the macro, it creates a line separating it from my Macro.

Dim Sht As Worksheet

Sub Test()
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Visible And Sht.Range("D1").Value = "Yes" Then
Call Hide_Blank_Rows
End If
Next Sht
End Sub

Sub Hide_Blank_Rows()

ActiveSheet.Range("$A$1:$A$700").AutoFilter Field:=1, Criteria1:="1"

End Sub

 

[JoeMo]

It only works for the sheet that I am on, it does not go through each subsequent sheet in my workbook.

Was I suppose to put the "Dim Sht As Worksheet" in a special location? When I include it in the macro, it creates a line separating it from my Macro.

Dim Sht As Worksheet

Sub Test()
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Visible And Sht.Range("D1").Value = "Yes" Then
Call Hide_Blank_Rows
End If
Next Sht
End Sub

Sub Hide_Blank_Rows()

ActiveSheet.Range("$A$1:$A$700").AutoFilter Field:=1, Criteria1:="1"

End Sub

Put it exactly where it was placed in my post, i.e. above the first sub so it is a variable in both subroutines. in your hide sub change activesheet to Sht

 

[Will85]

Put it exactly where it was placed in my post, i.e. above the first sub so it is a variable in both subroutines. in your hide sub change activesheet to Sht

Changing Activesheet.Range to Sht.Range now produces an error.

Dim Sht As Worksheet
Sub Test()
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Visible And Sht.Range("D1").Value = "Yes" Then
Call Hide_Blank_RowsV2
End If
Next Sht
End Sub

Sub Hide_Blank_RowsV2()

Sht.Range("$A$1:$A$700").AutoFilter Field:=1, Criteria1:="1"

End Sub

 

[JoeMo]

Changing Activesheet.Range to Sht.Range now produces an error.

Dim Sht As Worksheet
Sub Test()
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Visible And Sht.Range("D1").Value = "Yes" Then
Call Hide_Blank_RowsV2
End If
Next Sht
End Sub

Sub Hide_Blank_RowsV2()

Sht.Range("$A$1:$A$700").AutoFilter Field:=1, Criteria1:="1"

End Sub

Works fine for me. Please tell me what the error message is, and what line is highlighted when you get the error.

 

[Will85]

Works fine for me. Please tell me what the error message is, and what line is highlighted when you get the error.

Run time error 424, Object required.

See yellow highlight in previous response, its my called macro.

 

[JoeMo]

Run time error 424, Object required.

See yellow highlight in previous response, its my called macro.

I'm unable to reproduce your error so can't tell what is causing it. Can you post some sample data, enough to cause the error but no more than that, using XL2BB?
XL2BB: XL2BB - Excel Range to BBCode