Macro should run whenever a new sheet is added

[Penny Bangalore]

hello,

Need help please

i have a macro which will change the number format to currency format ( $ ) .. i want this to be happened whenever a new worksheets is added to workbook..

can anyone help me please


Sub Formatcurrency()
Dim iSht As Integer
Dim rng As Range


For iSht = 1 To Sheets.Count
Set rng = Worksheets(iSht).UsedRange
With rng
.NumberFormat = "$#,##0.00"
End With
Next
End Sub

 

[shg]

Why not just change style Normal to use that number format?

 

[Mike LH]

Hi,

In vb editor double click 'ThisWorkBook' and paste this in on the right.


Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim iSht As Long
Dim rng As Range
For iSht = 1 To Sheets.Count
Set rng = Worksheets(iSht).UsedRange
With rng
.NumberFormat = "$#,##0.00"
End With
Next
End Sub

 

[Penny Bangalore]

working thanks