Loop to infinity 1

[vba317]

I have created a loop that I can't get out of. I have a multiple worksheet excel file. With help I have created a loop that does work to loop thru all the worksheets. Now I want specific things to be done to one worksheet and not to the others. This is when my problems started.

Here is my code

/code/
Sub FormatLong()
'
' FormatLong Macro
' Macro recorded 3/6/2009

Dim ws As Worksheet

For Each ws In Worksheets
With ws
.Activate

If Sheets("BulkQuotesXL Settings").Select Then
Columns("A").Select
With Selection
.NumberFormat = "text"
.Columns.AutoFit
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Columns("B:C").Select
With Selection
.NumberFormat = "mm/dd/yyyy"
.Columns.AutoFit
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Columns("F").Select
With Selection
.NumberFormat = "#,##0"
.Columns.AutoFit
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlRight
End With

Else
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 = xlRight
End With

R1 = ActiveSheet.UsedRange.Rows.Count
R2 = "F" & R1
SR = R1 - 22
ActiveWindow.ScrollRow = SR
Range(R2).Select

End If

End With
Next

'
End Sub
/code/

 

[SkipVought]

Hi,

/code/

Use TGML Tage. Search on THIS PAGE for TGML and use the CODE tag.

Now I want specific things to be done to one worksheet and not to the others.

dim ws as worksheet
for each ws in worksheets
  with ws
    select case .name
       case "YourSpecialSheetName"
         'do something SPECIAL SHEET here
       case else  'all other sheets
         'do something for ALL OTHER SHEETS here
    end select
    'do something for ALL SHEETS here
  end with
next

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

 

[vba317]

I must have misunderstood the post on putting tags on code.
You want /code/ in front of all the lines?

Tom

 

[SkipVought]

Did you SEARCH on this page for the TGML LINK?

Did you see what my CODE posting looked like, using TGML Tags?

I did not say anything about using /code/.

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

 

[vba317]

I just clicked on the process TGML function on the bottom of the page. I did not see this before. This is the way I will submt my next question. As a newbie I am sorry. Thanks for all your help.

 

[SkipVought]

Nor problem. We all have to learn something new from time to time.

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

 

[mscallisto]

Skip

even you old dudes?

sam

 

[SkipVought]

Yep, sonny!

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

 

[fumei]

Especially us old dudes! I have forgotten more than I can remember.

Gerry