adding sheet name to sheet

Hi,

I need to add the name of each sheet in a workbook to the A2 cell of that workbook in a macro.

I've tried using

Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=MID(CELL(""filename""),(FIND(""]"",CELL(""filename""))+1),50)"


but it labels each sheet with an A2 value with the same sheet name

so if the workbook contains 3 sheets called

Sheet1, Sheet2, Sheet3

 the A2 value in Sheet1, Sheet2, Sheet3 ois

Sheet1
 
LINNANDAAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
dentabConnect With a Mentor Commented:
actually, my 1st solution should have done it - but nevermind
0
 
andylerooCommented:
You could create a user defined function.  Open the Visual Basic Editor (Alt + F11) and if you do not have one for your workbook create a module (right-click the Module folder and select Insert then Module).  Insert the following code in the module:

Public Function getSheetName() As String
    getSheetName = ThisWorkbook.ActiveSheet.Name
End Function

Press Alt + Q to return to the spreadsheet.  Select cell A2 and type the following:

=getSheetName()

Press enter and the name of the current sheet should be displayed.  One thing to note, if you change the name of the sheet you may need to press Ctrl + Alt + F9 to re-calculate.
0
 
dentabCommented:
I think you want something like this

Sub setA2()
  Dim sheetLoop As Long
  For sheetLoop = 1 To ThisWorkbook.Sheets.Count
    ThisWorkbook.Sheets(sheetLoop).Cells(2, 1) = ThisWorkbook.Sheets(sheetLoop).Name
  Next sheetLoop
End Sub

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
dentabCommented:
Note:
This could be called from workbook open
0
 
LINNANDAAuthor Commented:
Hi

i added

Public Function getSheetName() As String
    getSheetName = ThisWorkbook.ActiveSheet.Name
End Function

and Range("A2").Value = getSheetName()

and am getting

Sheet1 rather than the name of the shhet
0
 
dentabCommented:
have you tried my code?

Also, do you mean the name of the sheet or the name of the workbook?
0
 
andylerooCommented:
OK.  I think I miss read your requirements - sorry!

Try the following code for the getSheetName function:

Public Function getSheetName() As String
    Dim strSheetNames As String
    Dim strDelimiter As String
    
    'Set delimiter
    strDelimiter = " : "
    
    'Cycle through sheets and log their names - adding the delimiter
    For Each objSheet In ThisWorkbook.Worksheets
        strSheetNames = strSheetNames & objSheet.Name & strDelimiter
    Next
    
    'Remove the final " : " delimiter
    strSheetNames = Left(strSheetNames, Len(strSheetNames) - Len(strDelimiter))
    
    'Return the list of sheet names
    getSheetName = strSheetNames
End Function

Open in new window

0
 
dentabCommented:
doh! I did too - andyleroo has written quite an elegant solution to your actual question.

I'll leave it at that
0
 
LINNANDAAuthor Commented:
Hi andylerloo,

I added your code and got a run time error '424'

Object required.


on

Range("A1").Select = getSheetName()



Regards,

linnanda
0
 
dentabCommented:
I think it needs to be
and Range("A2").Value = getSheetName()
0
 
andylerooCommented:
I concur with dentab - this should do the trick.
0
 
LINNANDAAuthor Commented:
Hi,
I tried

Range("A2").Value = getSheetName()


and got back

Sheet1 : Sheet2 : Sheet3
0
 
dentabCommented:
is that not what you want?

as andyleroo said, the delimiter strDelimiter can be set by you to what you want.
You could set it to be

strDelimiter = vbCrLf

or something else
0
 
dentabCommented:
Sorry that last post was supposed to demondtrate multiple lines, it should be

strDelimiter = vbLf
0
 
dentabCommented:
Note: if that is what you want, please credit andyleroo - its not my solution, its his.
0
 
LINNANDAAuthor Commented:
Hi,

If I have five sheets called  GroupA, GroupB, GroupC, GroupD, GroupE

and run the above macro in sheeet GroupA,

I get Sheet1:Sheet2:Sheet3 in cell A2 rather than GroupA

if I run the above macro in sheeet GroupB

 I get Sheet1:Sheet2:Sheet3 in cell A2 rather than GroupB

0
 
dentabCommented:
the code works for me....

I have to check, the tabs at the bottom of the workbook are labelled
GroupA, GroupB, GroupC, GroupD, GroupE
?
0
 
dentabCommented:
If by name you mean you have set a title in say cell A1 then the code for the function would be as below
Public Function getSheetName() As String
    Dim strSheetNames As String
    Dim strDelimiter As String
    
    'Set delimiter
    strDelimiter = " : "
    
    'Cycle through sheets and log their names - adding the delimiter
    For Each objSheet In ThisWorkbook.Worksheets
        strSheetNames = strSheetNames & objSheet.Cells(1, 1).Value & strDelimiter
    Next
    
    'Remove the final " : " delimiter
    strSheetNames = Left(strSheetNames, Len(strSheetNames) - Len(strDelimiter))
    
    'Return the list of sheet names
    getSheetName = strSheetNames
End Function

Open in new window

0
 
LINNANDAAuthor Commented:
Hi dentab:,

I ran this and rather than GroupA appearing in A2 in the GroupA sheet I got

 :  :  :  :
0
 
dentabCommented:
Sorry, please check for me...

ARE the tabs at the bottom of the workbook are labelled
GroupA, GroupB, GroupC, GroupD, GroupE?

If this is not the case, then where is the title? My code before assumed cell A1
0
 
dentabCommented:
Could you attach a screenshot as a file please?
0
 
LINNANDAAuthor Commented:
Hi dentab,

I'l attach the workbook I'm using it contains 5 sheets

Sheet1 is labelled - GroupA

and GroupA is the value that I'm  trying to populate cell A1with in the sheet GroupA
Book1.xls
0
 
dentabCommented:
right scrap all the old macros

I tested this, put it in ThisWorkbook
Private Sub Workbook_Open()
  Dim wsLoop As Long
  Dim sheetNames() As String, strA2 As String
  
  ReDim sheetNames(ThisWorkbook.Sheets.Count - 1)
  For wsLoop = 1 To ThisWorkbook.Sheets.Count
     sheetNames(wsLoop - 1) = ThisWorkbook.Sheets(wsLoop).Name
  Next wsLoop
  
  strA2 = Join(sheetNames, " : ")
  
  For wsLoop = 1 To ThisWorkbook.Sheets.Count
    ThisWorkbook.Sheets(wsLoop).Cells(2, 1) = strA2
  Next
  
End Sub

Open in new window

0
 
LINNANDAAuthor Commented:
Hi dentab,

It's getting there but I'm now getting

GroupA : GroupB : GroupC : GroupD : GroupE

in every sheet

rather than

Group A in the GroupA sheet
GroupB in the GroupB sheet

etc.,


Book2.xls
0
 
dentabCommented:
lmao, I see my and andyleroo got what you want wrong

1 sec...
0
 
dentabCommented:

Private Sub Workbook_Open()
  Dim wsLoop As Long
   
  For wsLoop = 1 To ThisWorkbook.Sheets.Count
    ThisWorkbook.Sheets(wsLoop).Cells(2, 1) = ThisWorkbook.Sheets(wsLoop).Name
  Next
  
End Sub

Open in new window

0
 
LINNANDAAuthor Commented:
Thanks dentab that worked fine.
0
 
dentabCommented:
excellent, good luck with the rest of the sheet!
0
All Courses

From novice to tech pro — start learning today.