loop entire workbook Upper case

Jasen79

New Member
Joined
Nov 25, 2020
Messages
47
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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Fluff

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

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,108
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
47
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,865
Messages
5,627,341
Members
416,242
Latest member
Kas O

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