Help with code to process all worksheets

[bradjedis]

Greetings,
I have this code that Indents three columns of data. This all works,. What I need is to have the code cycle thru all worksheets. I gave it my attempt, but to no good.

pretty sure it is something easy..

Code: Select all

Sub AIndent()
    
    Dim w As Worksheet
    
    Dim r As Long
    Dim m As Long
    Dim varValue As Variant
    Application.ScreenUpdating = False
    
    For Each w In Worksheets
    
    Columns("B:D").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    m = Range("B" & Rows.Count).End(xlUp).Row
    For r = 1 To m
        varValue = Range("Bb" & r).Value
        If Not varValue = "" Then
            If IsNumeric(varValue) Then
                With Range("B" & r).Resize(ColumnSize:=3)
                    .HorizontalAlignment = xlLeft
                    .IndentLevel = varValue - 0
                End With
            End If
        End If
    Next r
  
  Next w

   Application.ScreenUpdating = True
End Sub

[HansV]

You have to refer to the sheet w consistently:

Code: Select all

Sub AIndent()
    Dim w As Worksheet
    Dim r As Long
    Dim m As Long
    Dim varValue As Variant
    Application.ScreenUpdating = False
    For Each w In Worksheets
        With w.Range("B:D")
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        m = w.Range("B" & w.Rows.Count).End(xlUp).Row
        For r = 1 To m
            varValue = w.Range("B" & r).Value
            If Not varValue = "" Then
                If IsNumeric(varValue) Then
                    With w.Range("B" & r).Resize(ColumnSize:=3)
                        .HorizontalAlignment = xlLeft
                        .IndentLevel = varValue
                    End With
                End If
            End If
        Next r
    Next w
    Application.ScreenUpdating = True
End Sub

[bradjedis]

Ahhhh ok.. I will file this one for reference.

Thanks!