loop entire workbook Upper case

[Jasen79]

Sub Captialcode()

' Declare Current as a worksheet object variable.
Dim Current As Worksheet

' Loop through all of the worksheets in the active workbook.
For Each Current In Worksheets

' Insert code here.
Dim rng As Range
For Each rng In ActiveSheet.UsedRange
rng.Value = UCase(rng.Value)
Next rng

' This line displays the worksheet name in a message box.
MsgBox Current.Name
Next

End Sub


I am trying to run the code through the whole workbook. I know the Upper case code works, I know the loop code works, each on their own. But together it would not. Any thoughts or insight you could provide.

 

[Fluff]

You need to change ActiveSheet to Current

 

[lrobbo314]

I would do it like this so you don't need to loop through each cell in the used range.

Sub Captialcode()

Dim ws As Worksheet
Dim rng As Range

For Each ws In ActiveWorkbook.Worksheets
    Set rng = ws.UsedRange
    rng.Value = Evaluate(Replace("UPPER(@)", "@", rng.Address))
    MsgBox ws.Name
Next

End Sub

 

[Jasen79]

Many Thanks! Worked perfectly!
Oddly the first time I ran it half my data up and vanished! Quit restated and the next two times work perfect.
Must have just been the Ghost in the Machine.
Happy Holidays and thank you again.