Solution Required

[Akash]

Hi,

I have a worksheet with the Sheet named as:

Item 1
Item 2
Item 3
Item 4
Item 5
Item 6

I want a code through which i can change the Work Sheet Name as

Item 1 Jan
Item 2 Jan
Item 3 Jan
Item 4 Jan
Item 5 Jan
Item 6 Jan

Require your guidance to over come this problem which i have so many
sheets in one file and i had to rename all sheets every month.

Awaiting for your response.

Regards

Akash Maheshwari

 

[EricG]

Place the following in a general Visual Basic Module. This should give you
an idea of how to do what you want. The subroutine renames both worksheets
and chart sheets. You can modify to suit your needs.

HTH,

Eric

Sub Worksheet_Name_Change()
Dim ws As Worksheet
Dim ch As Chart
'
For Each ws In ActiveWorkbook.Worksheets
ws.Name = ws.Name & " Jan"
Next ws
'
For Each ch In ActiveWorkbook.Charts
ch.Name = ch.Name & " Jan"
Next ch
'
End Sub

 

[Rod]

Open Visual Basic editor (via Tools, Macros
Do "View" followed by "Project Explorer"
Select ThisWorkbook from left hand window

paste in this code and run it from Tools, Macros

This will append the month you enter to all sheet names.
Maybe if you have other sheets you will next some logic to restrict to ones
called Item

_____________________
Option Explicit

Public Sub ReNameSheets()

Dim xlSht As Worksheet
Dim TheMonth As String

TheMonth = InputBox("Enter month")

For Each xlSht In Me.Worksheets
xlSht.Name = xlSht.Name & " " & TheMonth
Next xlSht

Set xlSht = Nothing

End Sub
__________________________

 

[RadarEye]

Hi Akash,

When you open de VBE (using Alt-F11) and make the properties visible
( using F4) you can see that each sheet has a property “(name)” and a
property “name”. The first is the internal name, the second is the
name shown on the tab to the users. You can use this in VBA:

Start with making the internal names similar as the names you want on
the tab, without the month abbreviation. Keep in mind that a space in
not allowed in this internal name. I suggest a underscore instead.
EG: Item_1, Item_2, etc.

Then create a vba program to change the shown name, something like:

Sub RemaneAllSheets()
' using References:
' Visual Basic for Applications
' Microsoft Excel Object Library
Dim strExternal As String
Dim shtLoopSheet As Worksheet

For Each shtLoopSheet In ThisWorkbook.Worksheets
With shtLoopSheet
If InStr(1, .CodeName, "Item") = 1 Then
strExternal = .CodeName & Format(Date, " mmm")
shtLoopSheet.Name = Replace(strExternal, "_", " ")
End If
End With
Next

End Sub

This will use the abbreviation of the current month

I used Excel 2007 to create the macro, but I am pretty sure it will
work in previous versions.
Hoop This Helps,

Radareye.

 

[Akash]

Hi,

Thanks for the support, but i am getting a error message as Invalid
use of ME keyword.

Akash

 

[Rod]

You don't need it, just use

For Each xlSht In Worksheets



Hi,

Thanks for the support, but i am getting a error message as Invalid
use of ME keyword.

Akash