Add file name to end of all sheet name

T

transferxxx

Does anybody has a macro which would add the file name of my active
workbook to all sheet name of the workbook
eg if file name is file.xls & sheetA & sheetB & sheetC

macro will rename sheets as foll:
sheetA - file (without extension ".xls")
sheetB - file
sheetC - file

thxs
 
T

transferxxx

Sub SheetnameANDWorkbook()
ActiveSheet.Name = ActiveSheet.Name & "-" & Left(ActiveWorkbook.Name,
Len(ActiveWorkbook.Name) - 4)
End Sub

can anybody debug above - as I having error with:
& "-" &

Thxs
 
N

Norman Jones

Hi TransferXXX,

Try:

'=============>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim sStr As String

Set WB = ActiveWorkbook
sStr = Left(WB.Name, Len(WB.Name) - 4)

For Each SH In WB.Worksheets
SH.Name = SH.Name & " - " & sStr
Next SH
End Sub
'<<=============
 
T

transferxxx

thxs work great !!
Norman said:
Hi TransferXXX,

Try:

'=============>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim sStr As String

Set WB = ActiveWorkbook
sStr = Left(WB.Name, Len(WB.Name) - 4)

For Each SH In WB.Worksheets
SH.Name = SH.Name & " - " & sStr
Next SH
End Sub
'<<=============
 
D

Dave Peterson

Maybe it's not the hyphen causing the trouble.

A worksheet name is limited to 31 characters and some characters cannot be used.

So what's the original name of the worksheet and what's the name of the
workbook?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top