loop entire workbook Upper case

Jasen79

New Member
Joined
Nov 25, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,620
Office Version
  1. 365
Platform
  1. Windows
You need to change ActiveSheet to Current
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,100
Office Version
  1. 365
Platform
  1. Windows
I would do it like this so you don't need to loop through each cell in the used range.

VBA Code:
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
 
Solution

Jasen79

New Member
Joined
Nov 25, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,720
Messages
5,626,477
Members
416,187
Latest member
L_D18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top