Run macro on specific sheets

**vicsar** · 08-16-2012, 04:03 PM

Hello, I have this code which formats specific cells on specific sheets, it works fine but when I use the Option Explicit argument if does not compile. How can I rewrite it so it compiles with Option Explicit? - Thanks.

Sub Format_Specific_Sheets()
On Error GoTo ErrHandler:

Sheetlist = Array("Hoja1", "Sheet1", "Sheet3", "Sheet4", "Sheet5") 'Define sheet names, to format
For i = LBound(Sheetlist) To UBound(Sheetlist)
Worksheets(Sheetlist(i)).Activate
' Your Code Goes Here
    Range("F4:I8").Select
    Range("I8").Activate
    Selection.Font.Bold = True
Next

ErrHandler:
    If Err.Number = 9 Then 'Sheet does not exist
        MsgBox "No more sheets fullfill the criteria. All operations completed succesfully", vbInformation, "Done"
    Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & ")", vbInformation, "Humm..."
        Exit Sub
    End If

End Sub

**Kelshaer** · 08-16-2012, 04:14 PM

Use this:

Option Explicit

Sub Format_Specific_Sheets()
On Error GoTo ErrHandler:
Dim Sheetlist As Variant
Dim i As Integer
Sheetlist = Array("Hoja1", "Sheet1", "Sheet3", "Sheet4", "Sheet5") 'Define sheet names, to format
For i = LBound(Sheetlist) To UBound(Sheetlist)
' Your Code Goes Here
    Worksheets(Sheetlist(i)).Range("F4:I8").Font.Bold = True
Next
Exit Sub
ErrHandler:
    If Err.Number = 9 Then 'Sheet does not exist
        MsgBox "No more sheets fullfill the criteria. All operations completed succesfully", vbInformation, "Done"
    Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & ")", vbInformation, "Humm..."
        Exit Sub
    End If

End Sub

**vicsar** · 08-16-2012, 05:27 PM

Khaled it works! I knew I had to declare Sheetlist I just did not know which option to use (Variant) and in one of my tries I had i as Integer but since the first was not declared VBA would not compile. Thanks.

Now here is another challenge I tried the code it compiles an runs fine but it return and error if one of the sheets is not found i.e.: I have Hoja1 but I don't have Sheet1. Sheet3 exists but the format would not change it as when it gets to the second sheet on the sheetlist it gives me the error message instead of probing for the next sheet name. How can we avoid this? What I am trying to do is have the same code run on Sheet1 OR Hoja1 OR Sheet3, etc. What's your suggestion on that?

**JBeaucaire** · 08-16-2012, 05:49 PM

Using the macro offered, we just need to test if the sheets exist before we try to act on them, so list all the possible sheet variations you want to try in the sheet array:

Sub Format_Specific_Sheets()
Dim Sheetlist As Variant, i As Long

    Sheetlist = Array("Hoja1", "Sheet1", "Sheet3", "Sheet4", "Sheet5") 'Define sheet names, to format

    For i = LBound(Sheetlist) To UBound(Sheetlist)
        If Evaluate("ISREF('" & Sheetlist(i) & "'!A1)") Then
            Worksheets(Sheetlist(i)).Range("F4:I8").Font.Bold = True
        End If
    Next i

End Sub