Rename Worksheet tab

[CAM]

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

 

[Chip Pearson]

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)

 

[Chip Pearson]

I didn't notice that you mentioned you were using Excel 2007. The code
I posted previously will work in 2007 and earlier, but will only look
at Excel 2003 files. If you have files in Excel 2007 format or a mix
of 2003 and 2007 files, use the following code instead.


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

' FolderName is the folder that contains the Excel files.
FolderName = "C:\Test" '<<< CHANGE
' set the current directory to FolderName
ChDrive FolderName
ChDir FolderName
FileName = Dir("*.*")
' Turn off screen updating for speed and aesthetics.
Application.ScreenUpdating = False
' Loop through all files in FolderName.
Do Until FileName = vbNullString
' Get the file extension of FileName.
Ext = Mid(FileName, InStrRev(FileName, "."))
Select Case LCase(Ext)
' Test if FileName is an Excel workbook. Supports
' both Excel 2003 and 2007 file formats.
Case ".xls", ".xlsm", ".xlsx", "xlsb"
' FileName is an Excel workbook. Open it.
Set WB = Workbooks.Open(FileName:=FileName)
' Create the new name from the workbook name.
' Chop off the extension.
S = Left(WB.Name, InStrRev(WB.Name, ".") - 1)
' Rename the first Worksheet to the modified file
' name. Ignore the error that might occur if
' a worksheet by that name already exists.
On Error Resume Next
WB.Worksheets(1).Name = S
On Error GoTo 0
' Save and close the workbook.
WB.Close savechanges:=True
Case Else
' not an Excel workbook
End Select
' Get the next file name from Dir. This will be an
' empty string 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)

 

[CAM]

Chip, Works like a charm. Thanks for the code I really appreciate your
help. Excellent code.

Cheers.