Add file name to end of all sheet name

[[email protected]]

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

[[email protected]]

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



[email protected] wrote:
> 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

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


---
Regards,
Norman


<[email protected]> wrote in message
news:[email protected]...
> 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
>

[[email protected]]

thxs work great !!
Norman Jones wrote:
> 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
> '<<=============
>
>
> ---
> Regards,
> Norman
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >

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

[email protected] wrote:
>
> 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
>
> [email protected] wrote:
> > 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

--

Dave Peterson