Need help in running the code in different sheets. Thanks

[pedie]

This is my current code:

Sub Left_format()
Dim i As Integer
For i = 1 To 4
With Sheets(i)
    With .Cells
        .HorizontalAlignment = xlLeft
 
    End With
End With
Next i
End Sub

I want this code to run in different 3 sheets, namely; "data", "Update","Tracer"
I have another sheets and I do not want the macro to run there.
THanks alot!

 

[VoG]

Try

Sub Left_format()
Dim ws As Worksheet
For Each ws In Sheets(Array("data", "Update", "Tracer"))
    With ws
        With .Cells
            .HorizontalAlignment = xlLeft
        End With
    End With
Next ws
End Sub

 

[pedie]

There you are again Vog!
Thanks again

 

[pedie]

When i try to run in different code it is giving error please check.

Sub Left_format()
Dim ws As Worksheet
For Each ws In Sheets(Array("sheet1", "sheet2", "sheet3"))
    With ws
        Range("A2").Select
        Selection.CurrentRegion.Select
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D2:D" & Selection.Rows.Count + 1) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
       .SetRange Range("A2:F" & Selection.Rows.Count + 1)
          .Header = xlGuess
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
          
    End With
Next ws
End Sub

 

[VoG]

This is untested

Sub Left_format()
Dim ws As Worksheet, x As Long
For Each ws In Sheets(Array("sheet1", "sheet2", "sheet3"))
    With ws
        With .Range("A2").CurrentRegion
            x = .Rows.Count + 1
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=Range("D2:D" & x) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With .Sort
            .SetRange Range("A2:F" & x)
              .Header = xlGuess
              .MatchCase = False
              .Orientation = xlTopToBottom
              .SortMethod = xlPinYin
              .Apply
            End With
        End With
    End With
Next ws
End Sub

 

[pedie]

Still giving me error messageruntime error 1004
unable to get sort property of range class

 

[VoG]

Now tested:

Sub Left_format()
Dim ws As Worksheet, x As Long
For Each ws In Sheets(Array("sheet1", "sheet2", "sheet3"))
    With ws
        With .Range("A2").CurrentRegion
            x = .Rows.Count + 1
            ws.Sort.SortFields.Clear
            ws.Sort.SortFields.Add Key:=Range("D2:D" & x) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With ws.Sort
            .SetRange ws.Range("A2:F" & x)
              .Header = xlGuess
              .MatchCase = False
              .Orientation = xlTopToBottom
              .SortMethod = xlPinYin
              .Apply
            End With
        End With
    End With
Next ws
End Sub

 

[pedie]

Great thanks alot!

 

[VoG]

You are welcome.

I find the XL 2007 code for sorting to be rather kludgy. Try recording a sort using Excel 2003 and below - the code is a lot cleaner (and still works in XL 2007+).

 

[pedie]

Okay. Thanks Vog!