How to return the first letter of a worksheet?

[JGKWORK]

Hi,

In excel, how would I return the first letter of a work sheet? E.G. if my sheet is named "Firstsheet" the code would return the letter "F".

Many thanks...

 

[FXP]

Sub FirstLeteer()
'''Return First Letter of Active Sheet Name
Dim FirstLetter As String

FirstLetter = Left(ActiveSheet.Name, 1)
MsgBox FirstLetter

End Sub

 

[JGKWORK]

Excellent, many thanks!

 

[KenWright]

No need for code

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,1)

Workbook must be saved at least once for it to work

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[JGKWORK]

I want it to return the first letter of each worksheet but it returns the first letter of the initial active sheet each time, how can I get it to move to the next sheet?:


Dim FirstLetter As String
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

FirstLetter = Left(ActiveSheet.Name, 1)
MsgBox FirstLetter

Next

End Sub

Many thanks....

 

[KenWright]

Sub ShtNames()
For x = 1 To ActiveWorkbook.Sheets.Count
MsgBox Left(Sheets(x).Name, 1)
Next
End Sub

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[JGKWORK]

Many thanks, will give that a go!

 

[JGKWORK]

Thanks alot thast worked great, final thing, the code below only finds a Capital letter "C", how do i search without distinction between capital and non capital letters?

If Left(Sheets(x).Name, 1) = "C" Then

Many thanks...

 

[mrmovie]

i tend to only deal with upper or lower case, so before you do an text comparisons always do this

If UCase(Left(Sheets(x).Name, 1)) = "C" Then

 

[JGKWORK]

The problem I'm facing is that a spreadsheet arrives to my work and I have to check and change the spelling on each worksheet. So, when the spreadsheet arrives it maybe uppercase or lowercase, I need to check for the first letter irrespective of case...please help!

 

[mrmovie]

ok, when you use the "=" operator to compare 2 strings there is no way of saying 'ignore case'.
therefore if you want to do a check ignoring case you need to convert both strings to either upper or lower case before you do the comparison, so....

strX = H
strY = h

Both

If UCase(strX, 1)) = "H"

and

If UCase(strY, 1)) = "H"

evaluate to true.

So, all you need to do is use the UCase() method which i suggested.
This will work for however the sheet names have been formated, upper/lower or mixed case

 

[TonyJollans]

JGKWORK,

Look again at mrmovie's post and check out the UCase and LCase Functions.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[JGKWORK]

Yip, cheers, workin!