New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Change worksheet tab name

Macros, VBA, Excel Automation, etc.

Change worksheet tab name

Postby Lazmail » Thu Mar 24, 2016 2:08 am

Thank No Sparks,
Both versions worked well.
Thank you very much.

I know I am pushing it a bit but can you also give me a bit of an inkling as to how to use a macro to change the Worksheet Tab name.
I have the need to change the range of Sheet Tab names (SJan16, Sfeb16------------ Sdec16) to the sheet Tab names to ( SJan17, SFeb17 ---------SDec17).
I need to have this happen either manually or automatically after the date has changed from the 30/6/"YY" to the 1st of July ( the beginning of the financial year) or when I choose to manually change the operative date.

As the programme will not be running continuously the Manual option would be the preferred choice.

I still need to save the information that is contained on each respective sheet and then to have the pertinent cells cleared of all data before the Renaming takes place.

Hopefully this will be my last request for quite some time I hope.
It is nice to have such a wealth of information available at such short notice, I thank you once again.
  • 0

Lazmail
Rookie
 
Posts: 14
Joined: Feb 3, 2015
Reputation: 0
Excel Version: 2007

Re: Change worksheet tab name

Postby NoSparks » Thu Mar 24, 2016 9:41 am

Don't know how or where existing sheet data is to be saved.
Don't know what cells need cleared.
Don't know if there's other sheets in the workbook and if there is don't know how they are named.

This will change the names of the worksheets you've indicated without hard coding the year

Code: Select all
Sub Lazmail_RenameSheets()
  Dim i As Integer
  Dim ws As Worksheet
 
  For i = 1 To Worksheets.Count
    If Right(Sheets(i).Name, 2) = Right(Year(Date), 2) Then
      Set ws = Sheets(i)
      With ws
        'check it's not named Sheetxx
        If Left(.Name, 5) <> "Sheet" Then
          .Name = Replace(.Name, Right(Year(Date), 2), Right(Year(Date), 2) + 1)
        End If
      End With
    End If
  Next i
 
End Sub

Hope this helps
NoSparks
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: Change worksheet tab name

Postby Lazmail » Thu Mar 24, 2016 6:15 pm

Good Morning No Sparks,
Thank you for your reply.
I will probably save the Worksheet as a .pdf and then clear the a range of data such as range (A4: J100) then change the Tab name from S(MMM)(YY ) to S(MMM)(YY +1).
There are a total of 72 worksheets in this workbook all named as shown in the above and as per the list below.
SJan16 through to SDec16
RecJan through to RecDec
Membership Receipt
Donation Receipt
Fundraising Receipt
Miscellaneous Receipt
Sales Receipt
DatabaseManager
Main Menu

Plus quite a few other sheets but all named in similar fashion. There is no reference to the system Sheet number and all of my Macros refer to the actual sheet names as per the respective Sheet Tab.
I propose to save the work book as that for the financial year eg. for the yea 2016 - 2016 etc and to increase the numbers for the subsequent years by increasing the numbers to 2016 - 2017.

Will the Lazmail_RenameSheets() Macro sort through the list of sheet names and look for SJan16 or SMay16 or does this have to be done for each sheet that I want to rename.

Will it try to rename other sheets as well or can I insert a range of names and only have this range renamed.
  • 0

Lazmail
Rookie
 
Posts: 14
Joined: Feb 3, 2015
Reputation: 0
Excel Version: 2007


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 54 guests