Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solution Required
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solution Required
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 "Akash" wrote: 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solution Required
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 __________________________ "Akash" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solution Required
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solution Required
Hi,
Thanks for the support, but i am getting a error message as Invalid use of ME keyword. Akash On Dec 15, 7:10*pm, "Rod" wrote: 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 __________________________ "Akash" wrote in message ... 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 AkashMaheshwari |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solution Required
You don't need it, just use
For Each xlSht In Worksheets "Akash" wrote in message ... Hi, Thanks for the support, but i am getting a error message as Invalid use of ME keyword. Akash On Dec 15, 7:10 pm, "Rod" wrote: 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 __________________________ "Akash" wrote in message ... 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 AkashMaheshwari |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Solution required | Excel Programming | |||
vba solution | Excel Programming | |||
VBA solution required? | Excel Programming | |||
odd vlookup solution required - can you rise to the challenge?! | Excel Programming | |||
Solution Required to prevent #Ref! when rows deleted | Excel Worksheet Functions |