Auto Format Dynamic Range on Dynamic Sheets on Workbook Open

[reberryjr]

I'm trying to auto format the data in a dynamic range, on a dynamic count of sheets, when the workbook opens. Essentially, every sheet not named "Variables" or "Financials" where there is data in any sequential row. I'm using this code, but it's not hitting all of the sheets. What am I missing here?

Private Sub Workbook_Open()

Application.ScreenUpdating = False


Dim ws As Worksheet


For Each ws In Worksheets
    If Not ws.Name = "Financials" And Not ws.Name = "Variables" Then
        Range("A1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        With Selection
            .HorizontalAlignment = xlCenter
        End With
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
                With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        Cells.Select
        Cells.EntireColumn.AutoFit
    End If
Next ws
Application.ScreenUpdating = True


End Sub

 

[Fluff]

You haven't qualified the ranges/cells so it's only working in the active sheet. Try adding this line

If Not Ws.Name = "Financials" And Not Ws.Name = "Variables" Then
   [COLOR=#ff0000]Ws.Activate[/COLOR]
   Range("A1").Select

 

[mumps]

The original code (with Fluff's amendment) could be shortened to this:

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Worksheets
        If Not ws.Name = "Financials" And Not ws.Name = "Variables" Then
            ws.Activate
            With ws.UsedRange.Cells
                .HorizontalAlignment = xlCenter
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeTop).LineStyle = xlContinuous
                .Borders(xlEdgeBottom).LineStyle = xlContinuous
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlInsideVertical).LineStyle = xlContinuous
                .Borders(xlInsideHorizontal).LineStyle = xlContinuous
                .EntireColumn.AutoFit
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

Or, even shorter,

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Worksheets
        If Not ws.Name = "Financials" And Not ws.Name = "Variables" Then
            With ws.UsedRange.Cells
                .HorizontalAlignment = xlCenter
                .Borders.LineStyle = xlContinuous
                .EntireColumn.AutoFit
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[reberryjr]

Thank you both!

 

[Fluff]

Glad we could help