VBA to rename Tab by merging file name

[excelsishya]

Hi all ,

Could anyone provide code which renames tabs in all excel file in folder by adding last 4 letter of file name to tab.

Have excel file with jan feb etc tab name.file name is income statement 2008.xls.

after running code desired output jan2009 feb2009.

I am using excel 2007.

Also after naming each of tab is it possible move all tabs to master file.

Thanks in advance.

 

[Yahya]

try this code
it is working just with open workbook

Sub Test()
Dim ws As Worksheet
For Each ws In Sheets
    ws.Name = ws.Name & Mid(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 8, 4)
Next ws
End Sub

 

[excelsishya]

Yahya thanks for code it works fine.

Also if there is code to do same thing without opening file please provide it.


if file has all 12 months tabs , then it becomes easy to do opening running code.

in some cases i also come across file with only 1 month tab in such case i have to open 36-48 tabs run code .for such cases i am looking for 2 type of code.

 

[excelsishya]

Refresh,

Any thoughts