Macro to capture worksheet names

E

El Bee

I have a spreadsheet that gets clobbered from time to time by data entry
folks. Part of the problem is a macro that can't find a specific worksheet
because the name may have been changed.
Is there a way, using a macro that can look at each worksheet and capture
it's name and then paste that name into a cell somewhere on a specific
worksheet.

Just trying to make my day a bit easier.
 
F

Franz Verga

El said:
I have a spreadsheet that gets clobbered from time to time by data
entry folks. Part of the problem is a macro that can't find a
specific worksheet because the name may have been changed.
Is there a way, using a macro that can look at each worksheet and
capture it's name and then paste that name into a cell somewhere on a
specific worksheet.

Just trying to make my day a bit easier.
Click to expand...

Maybe this can help you...

This formula gives the name of the worksheet in which it's written:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
E

El Bee

Actually what I was looking for, I found by doing a different search on this
site.

Here's what I found (it as been modified to meet my needs) Only the cell
locations were changed the formula is the same. The "FOR" loop is what I was
looking for.

Thanks for your time and help.

Sub Load_WK_Sheet_Names()
Dim SH As Object
Dim i As Long

Application.Goto Reference:="worksheet_names"
Selection.ClearContents

For Each SH In ThisWorkbook.Sheets
With SH
If UCase(.Name) <> "BY NAME" Then
i = i + 1
Sheets("By Number").Range("BH3")(i).Value = .Name
Else
Range("C2").Select
Exit Sub
End If
End With
Next SH
End Sub




Franz Verga said:
El said:
I have a spreadsheet that gets clobbered from time to time by data
entry folks. Part of the problem is a macro that can't find a
specific worksheet because the name may have been changed.
Is there a way, using a macro that can look at each worksheet and
capture it's name and then paste that name into a cell somewhere on a
specific worksheet.

Just trying to make my day a bit easier.
Click to expand...

Maybe this can help you...

This formula gives the name of the worksheet in which it's written:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
Click to expand...
 
Top