ALL CAPS code help

[harryco79]

Hi guys.
I am looking to convert all the text/characters in an excel workbook into all CAPS. The workbook has several worksheets with data in them already.

I found this code below from a previous post on this board and tried to use it in the workbook module but nothing seemed to happen

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim c As Range
Application.EnableEvents = False
For Each c In Target.Cells
c = UCase(c.Text)
Next
Application.EnableEvents = True
End Sub

I would like a code that i can use once after that i can delete it (without loosing the ALL CAPS format)

Thanks Guys

 

[VoG]

Hi. Try

Sub ToCaps()
Dim ws As Worksheet, c As Range
For Each ws In ActiveWorkbook.Worksheets
    With ws
        For Each c In .UsedRange
            With c
                .Value = UCase(.Value)
            End With
        Next c
    End With
Next ws
End Sub

 

[harryco79]

Wow thanks for the quick Reply. Will try and report back

 

[harryco79]

VoG YOU TOTALLY ROCK!
Worked like a charm. Took a second to run but result is perfect.

Cheers!