set code to ALL sheets in workbook ?
 

Sub SentenceCase()
For Each cell In Range("a8:A30,A7:Q7")
s = cell.Value
Start = True
For i = 1 To Len(s)
ch = Mid(s, i, 1)
Select Case ch
Case "."
Start = True
Case "?"
Start = True
Case "a" To "z"
If Start Then ch = UCase(ch): Start = False
Case "A" To "Z"
If Start Then Start = False Else ch = LCase(ch)
End Select
Mid(s, i, 1) = ch
Next
cell.Value = s
Next
End Sub

The above code changes the font case to Sentance Case.
I placed this in a MODULE, but how can i get it to do this across ALL worksheets in the workbook?

Corey....

set code to ALL sheets in workbook ?
 

One way is to set a loop.
Dim sh as Sheets

For sh = 1 to Sheets.Count
sh.activate
' Do your tasks
Next sh

I am not sure if selecting all sheets will accomplish this.

--
Best wishes,

Jim


"Corey" wrote:

Sub SentenceCase()
For Each cell In Range("a8:A30,A7:Q7")
s = cell.Value
Start = True
For i = 1 To Len(s)
ch = Mid(s, i, 1)
Select Case ch
Case "."
Start = True
Case "?"
Start = True
Case "a" To "z"
If Start Then ch = UCase(ch): Start = False
Case "A" To "Z"
If Start Then Start = False Else ch = LCase(ch)
End Select
Mid(s, i, 1) = ch
Next
cell.Value = s
Next
End Sub

The above code changes the font case to Sentance Case.
I placed this in a MODULE, but how can i get it to do this across ALL worksheets in the workbook?

Corey...

set code to ALL sheets in workbook ?
 

Corey,

Dim WS as Worksheet
dim cell as range

for each ws in thisworkbook.worksheets
for each cell in ws.range("a8:A30,A7:Q7")
... your code

NickHK

"Corey"
...
Sub SentenceCase()
For Each cell In Range("a8:A30,A7:Q7")
s = cell.Value
Start = True
For i = 1 To Len(s)
ch = Mid(s, i, 1)
Select Case ch
Case "."
Start = True
Case "?"
Start = True
Case "a" To "z"
If Start Then ch = UCase(ch): Start = False
Case "A" To "Z"
If Start Then Start = False Else ch = LCase(ch)
End Select
Mid(s, i, 1) = ch
Next
cell.Value = s
Next
End Sub

The above code changes the font case to Sentance Case.
I placed this in a MODULE, but how can i get it to do this across ALL
worksheets in the workbook?

Corey....