VBA Change Format of All Cells in a Workbook

  • Thread starter Thread starter benatom
  • Start date Start date
B

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.
 
One way:

Dim ws As Worksheet
For Each ws in ActiveWorkbook.Worksheets
ws.Cells.NumberFormat = "General" ' or "@" for Text
Next ws
 
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
 
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.
 
As others have pointed out, Cells is better to use than my
Range("1:"&WS.Rows.Count).
 
Back
Top