Add file name to end of all sheet name

[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

 

[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

 

[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
'<<=============

 

[transferxxx]

thxs work great !!

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
'<<=============

 

[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?