question of loops 2

[vba317]

I have a routine that I am trying to streamline by using a loop. I have never worked with loops before so I could use some help.

Here is my code:

Sheets("GLD").Select
    Rows("2:2").Select
    [ActiveWindow.FreezePanes = True
    
    Columns("A").Select
        With Selection
        .NumberFormat = "mm/dd/yyyy"
        .Columns.Autofit
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        End With
    Columns("B:E").Select
        With Selection
        .NumberFormat = "0.00"
        .Columns.Autofit
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        End With
    Columns("F").Select
        With Selection
        .NumberFormat = "#,##0"
        .Columns.Autofit
        .VerticalAlignment = xlCenter
        .HorizontalAlignment = xlLeft
         End With
    R1 = ActiveSheet.UsedRange.Rows.Count
    R2 = "F" & R1
    SR = R1 - 22
    ActiveWindow.ScrollRow = SR
    Range(R2).Select
    
This particular excel file has 10 worksheets in it. Currently I have repeated this code ten times with the different sheet names on the top. Is there a way to loop through all the worksheets using the same code? Any help woould be appreciated. This is my first time posting so if I have done something wrong please advise.

 

[SkipVought]

Hi,

    Dim ws As Worksheet
    
    For Each ws In Worksheets
        With ws
            .Activate
            .Rows("2:2").Select
            ActiveWindow.FreezePanes = True
            
            With .Columns("A")
                .NumberFormat = "mm/dd/yyyy"
                .Columns.AutoFit
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
            End With
        
            With .Columns("B:E")
                .NumberFormat = "0.00"
                .Columns.AutoFit
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
            End With
        
            With .Columns("F")
                .NumberFormat = "#,##0"
                .Columns.AutoFit
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = xlLeft
             End With
            R1 = .UsedRange.Rows.Count
            R2 = "F" & R1
            SR = R1 - 22
            ActiveWindow.ScrollRow = SR
            .Range(R2).Select
        End With
    Next

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

a starting point:

For strSheet In Array("GLD", "another", ..., "yadda")
  With Sheets(strSheet)
    .Activate
    .Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    With .Columns("A")
      .NumberFormat = "mm/dd/yyyy"
      .Columns.Autofit
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlCenter
    End With
    With .Columns("B:E")
      .NumberFormat = "0.00"
      .Columns.Autofit
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlCenter
    End With
    With .Columns("F")
      .NumberFormat = "#,##0"
      .Columns.Autofit
      .VerticalAlignment = xlCenter
      .HorizontalAlignment = xlLeft
    End With
    R1 = .UsedRange.Rows.Count
    ActiveWindow.ScrollRow = (R1 - 22)
    .Range("F" & R1).Select
  End With
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, didn't refresh before submit ...

 

[SkipVought]

Somtimes, you may want to do your process for every sheet except a few...
code]
dim ws as worksheet

for each ws in worksheets
with ws
select case .name
case "Summary","Factors"
'do NADA for these sheets
Case Else
'do your thing here

end select
end with
next
[/code]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}