I'm not entirely sure what you are looking for (and a bit of
punctuation in the message would have been helpful), but you might be
able to use or adapt the following code. It looks in the folder named
"C:\Test" (change this to the appropriate folder), and pulls out all
the Excel workbooks (those files ending with ".xls"). It then renames
the first worksheet in each workbook with the name of the workbook
book (sans the "xls" extension). So, for example, the first worksheet
in the workbook "Florida.xls" will be renamed to "Florida".

Sub AAA()
Dim FolderName As String
Dim FileName As String
Dim S As String
Dim WB As Workbook

' FolderName is the folder that contains the Excel files.
FolderName = "C:\Test" '<<< CHANGE
' set the default directory to FolderName
ChDrive FolderName
ChDir FolderName
FileName = Dir("*.xls")
' turn off screen updating for speed and aesthetics
Application.ScreenUpdating = False
' loop through all XLS files
Do Until FileName = vbNullString
' open the workbook
Set WB = Workbooks.Open(FileName:=FileName)
' create the new name from the workbook name
S = Left(WB.Name, InStrRev(WB.Name, ".") - 1)
' rename the first worksheet ignore the error
' that might occur if a sheet with the name
' in S already exists.
On Error Resume Next
WB.Worksheets(1).Name = S
On Error GoTo 0
' save and close the workbook
WB.Close savechanges:=False
' get the next file name. this will be
' vbNullString when all files have be processed.
FileName = Dir()
Loop
' turn screen updating back on
Application.ScreenUpdating = True
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 19 Feb 2009 21:51:06 -0800, "CAM"
wrote:

Hello,

I have a many different workbooks that are name differentlly by individual
states for example I have a workbook call Florida I want to have the
worksheet name by the file name How do I do that? Any tips will be
appreciated. I am using excel 2007 and I just don't know the vba code to do
that. Thank you in advance.

Cheers