VBA Change Format of All Cells in a Workbook

[benatom]

With VBA I would like to change the format of all cells in all
worksheets of a workbook to 'General' or 'Text'. How can this be
done? Thanks.

 

[JE McGimpsey]

One way:

Dim ws As Worksheet
For Each ws in ActiveWorkbook.Worksheets
ws.Cells.NumberFormat = "General" ' or "@" for Text
Next ws

 

[Joel]

Sub test()

For Each sht In Sheets

sht.Cells.NumberFormat = "General"

Next sht


End Sub


or

Sub test()

For Each sht In Sheets

sht.Cells.NumberFormat = "@" 'for text

Next sht


End Sub

 

[Rick Rothstein]

This should do it

Sub ChangeWorkbookFormat()
Dim WS As Worksheet
For Each WS In Worksheets
WS.Range("1:" & WS.Rows.Count).NumberFormat = "General"
Next
End Sub

Use "@" instead of "General" if you want the Text format.

 

[Rick Rothstein]

As others have pointed out, Cells is better to use than my
Range("1:"&WS.Rows.Count).