PDA

View Full Version : [SOLVED] VBA MACRO CODE FOR getting sheet names at once in multiple sheets



Dharani
04-22-2014, 05:57 AM
Hello Everyone,

I would like to get the sheet name in the first column of all the sheets. (highlighted one...) automatically at once by one click.
please do the needful.
Please provide me some macro code for this...


I have attached the excel file where i did manually for your easy understanding and reference.


Thank you...

Bob Phillips
04-22-2014, 06:05 AM
Public Sub Insertname()
Dim ws As Worksheet
Dim lastrow As Long

For Each ws In ThisWorkbook.Worksheets

lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
ws.Columns(1).Insert
ws.Range("A1").Resize(lastrow).Value = ws.Name
ws.Columns(1).AutoFit
Next ws
End Sub

Dharani
04-22-2014, 08:19 PM
Public Sub Insertname()
Dim ws As Worksheet
Dim lastrow As Long

End Sub


Hello xld,
The code is awesome... really it works beautifully.... Thank you so much...
Thank you for spending your valuable time in helping me...
I have got a small doubt, now i am getting the tabs name, instead if i want to get my file name, like in which part of code i need to modify. is it possible??

Thank you...

Regards,
Dharani.

Bob Phillips
04-23-2014, 01:31 AM
Change


ws.Range("A1").Resize(lastrow).Value = ws.Name

to


ws.Range("A1").Resize(lastrow).Value = ws.Parent.Name

snb
04-23-2014, 03:17 AM
Sub M_snb()
Sheets(1).Cells(1) = ThisWorkbook.FullName
Sheets.FillAcrossSheets sheets(1).Cells(1)
End Sub

Dharani
04-23-2014, 05:52 AM
Change


ws.Range("A1").Resize(lastrow).Value = ws.Name

to


ws.Range("A1").Resize(lastrow).Value = ws.Parent.Name

Thanks a lot xld... the code is awesome... thank you so much for spending your valuable time in helping me...

Regards,
Dharani.

Jan Karel Pieterse
04-24-2014, 12:41 AM
FillAcrossSheets

That is what I love about forums, you learn new stuff every time!