Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to capture worksheet names
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to capture worksheet names
El Bee wrote:
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. 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to capture worksheet names
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" wrote: El Bee wrote: 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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help updating my macro to include a 2nd worksheet. | Excel Worksheet Functions | |||
Tracking worksheet names in functions | Excel Worksheet Functions | |||
Comparing a list to a Calendar worksheet. | Excel Worksheet Functions | |||
I need help with a macro which will copy a worksheet and.. | Excel Worksheet Functions | |||
Dynamic Worksheet Names | Excel Worksheet Functions |