Formatting every sheet in workbook

[georgecomstock]

I thought that the following code would apply formatting to every sheet in the current workbook, but it seems to be applying it only to the current active sheet. Could someone please explain why and provide a solution, many thanks.

Public Sub formatold()
Dim ws As Worksheet
For Each ws In Worksheets
 Range("A:af").Select
  Selection.Font.Size = 8
   Range("a:A").Select
   Selection.NumberFormat = "dd-mmm-yyyy"
    Range("p:p").Select
    Selection.NumberFormat = "0.00"
     Range("s:s").Select
    Selection.NumberFormat = "0.00"
     Range("t:t").Select
    Selection.NumberFormat = "0.00"
     Range("v:v").Select
    Selection.NumberFormat = "0.00"
     Range("y:y").Select
    Selection.NumberFormat = "0.00"
     Range("z:z").Select
    Selection.NumberFormat = "0.00"
     Range("ab:ab").Select
      Selection.NumberFormat = "0.00"
 
Next ws
End Sub

 

[mikerickson]

I'm away from my Excel program at the moment, but the WorkSheets object has a .CopyAcrossSheets (?) method that allows you to copy values, formulas or formatting across any Worksheets object, including ThisWorkbook.Worksheets.

Get one sheet, format it the way that you want and then use that method to transfer the formating to all sheets in the workbook.

 

[kpark91]

Try

Public Sub formatold()
Dim ws As Worksheet
For Each ws In Worksheets
With ws
 .Range("A:af").Font.Size = 8
   .Range("a:A").NumberFormat = "dd-mmm-yyyy"
    .Range("p:p").NumberFormat = "0.00"
     .Range("s:s").NumberFormat = "0.00"
     .Range("t:t").NumberFormat = "0.00"
     .Range("v:v").NumberFormat = "0.00"
     .Range("y:y").NumberFormat = "0.00"
     .Range("z:z").NumberFormat = "0.00"
     .Range("ab:ab").NumberFormat = "0.00"
 End With
Next ws
End Sub

 

[kpark91]

I believe the reason why it was happening was because you put the code in your activeworksheet module.

When that happens, if you do not specify which worksheet it is when you're calling "Range" or other stuff, it will always happen in that worksheet. So, you would have to specify the worksheets which I have done with "With ws" code.

 

[georgecomstock]

KPark91 your solution works wonderfully! Many thanks.

 

[mikerickson]

I'm glad you found a solution. As a follow up to my earlier post, the method is the .FillAcrossSheets method. This code uses that approach

Sub test()
With ThisWorkbook
    With .WorkSheets(1)
        .Range("A:af").Font.Size = 8
        .Range("a:A").NumberFormat = "dd-mmm-yyyy"
        .Range("p:p").NumberFormat = "0.00"
        .Range("s:s").NumberFormat = "0.00"
        .Range("t:t").NumberFormat = "0.00"
        .Range("v:v").NumberFormat = "0.00"
        .Range("y:y").NumberFormat = "0.00"
        .Range("z:z").NumberFormat = "0.00"
        .Range("ab:ab").NumberFormat = "0.00"
    End With
    .Worksheets.FillAcrossSheets Range:=.WorkSheets(1).Range("A:AF"), Type:=xlFillWithFormats
End With
End Sub

 

[georgecomstock]

Mike thank you for taking the trouble to provide a solution. I wish I could appreciate the merits of the two similar but subtely different solutions, but as I could not even fashion a solution for myself I am at a loss. Thanks again.