Solved Mac Excel, multi worksheet file, tab name is running numbers

November 3, 2014 at 08:59:01
Specs: Macintosh
I desperately need a macro to show sheet(tab) name in a fixed cell(e.g. J8) at the same sheet, and copy most left sheet to left side with auto rename tab name by adding 1, and the cell value(J8) will increase by 1. Thanks you very much

See More: Mac Excel, multi worksheet file, tab name is running numbers

Report •


#1
November 3, 2014 at 09:21:09
✔ Best Answer
I don't have a MAC to test this on, but it works on a Win 7 machine running Excel 2010.

This code assumes that the current Sheet1 (left most sheet) has a numerical name, e.g. 1 or 205 or 15.2, etc.

If that's not the case, you'll need to supply some more details.

Sub AddSheet()
'Add Sheet as new Sheet1
 Sheets.Add Before:=Sheets(1)
'Rename sheet using Sheet2.Name + 1
  ActiveSheet.Name = Sheets(2).Name + 1
'Place sheet name in J8
   ActiveSheet.Range("J8") = ActiveSheet.Name
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 4, 2014 at 06:41:30
Thanks DerbyDad03, it works perfectly, although I intend to copy left most sheet to left instead of add new sheet, then rename it and place sheet name in J8, I combine your reply with another from other source on how to copy sheet, now it's working smoothly. Thank you guys, thanks internet, I know nothing about macro and VB, I have been using those steps for years but never think of macro to do it in one shortcut, thanks once again, below it's the code working for me in case anyone else may need it in the future:

ActiveSheet.Select
ActiveSheet.Copy Before:=Sheets(1)
'Rename sheet using Sheet2.Name + 1
ActiveSheet.Name = Sheets(2).Name + 1
'Place sheet name in J8
ActiveSheet.Range("J8") = ActiveSheet.Name

End Sub

message edited by Jackcai


Report •

#3
November 4, 2014 at 18:56:35
I'm glad you were able to come up with a solution. I have 3 comments to offer:

1 - There is no need to Select the ActiveSheet with the first line of the code. The mere fact that the ActiveSheet is the active sheet is enough to perform operations on it without the need to Select it.

2 - If you are always Copying the first sheet (i.e. the left most sheet) then your version of the code requires that the first sheet be the ActiveSheet when you run the code. If the first sheet is not the ActiveSheet, then the code will copy whatever sheet is Active and you might not get the results you want. To truly automate this process, you can change the code to directly access the first sheet, thereby being able to run the code regardless of what sheet is active and still get the results you want.

Sub CopySheet()
'Copy the first sheet and make it the new first sheet
    Sheets(1).Copy Before:=Sheets(1)
'Rename the new sheet using Sheet2.Name + 1
    ActiveSheet.Name = Sheets(2).Name + 1
'Place sheet name in J8
    ActiveSheet.Range("J8") = ActiveSheet.Name
End Sub

3 - If you want to learn a little bit more about VBA, specifically macros for Excel, spend some time with this tutorial.

http://www.computing.net/howtos/sho...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
November 27, 2014 at 07:59:47
Hi DerbyDad03, thank you for your generous input, if I want keep all the current function, plus another one: after copy, sometime I need to manually change the tab name to another number, can I make the number in J8 change to tab name automatically?

Report •

#5
November 30, 2014 at 20:03:44
It would be easier to automate this if you would be willing to change the value in J8 and have it update the sheet name vs. changing the sheet name and have it update J8.

The Worksheet_Change event can be used to run a macro whenever a specific cell is changed. Therefore changing J8 could trigger a macro the change the sheet name.

For example, this code will change the name of the sheet to whatever is entered in J8, assuming that it is a legal sheet name. (Error handling could be added to deal with situations where an illegal name was entered in J8)

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$J$8" Then
      Me.Name = Range("J8")
   End If
End Sub

However, there is no WorksheetName_Change event, so there is no direct way to have J8 change when the sheet name is changed. Some other action would to be taken, such as activating another sheet.

For example, if this code was used, you could change the sheet name but J8 would not be updated until the sheet was Deactivated by clicking the sheet tab for another sheet:

 Private Sub Worksheet_Deactivate()
  Range("J8") = Me.Name
 End Sub

Let me know if either of those options sound like they would work for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •


Ask Question