Copy worksheet name into cell A1?
Ok, so I just want to copy the name from a worksheet into the cell A1, need this to work relative across 20 worksheets. Also in the same macr is it possible to delete the last 4 characters in the worksheet name? The worksheet was originally named from the file name and so has .xl at the end of the name. Any help gratefully appreciated. Patric -- crowdx4 ----------------------------------------------------------------------- crowdx42's Profile: http://www.excelforum.com/member.php...fo&userid=3774 View this thread: http://www.excelforum.com/showthread.php?threadid=57365 |
Copy worksheet name into cell A1?
Hi Patrick,
Try something like: '============= Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Set WB = Workbooks("YourBook.xls") '<<==== CHANGE For Each SH In WB.Worksheets With SH .Name = Left(.Name, Len(.Name) - 4) .Range("A1").Value = .Name End With Next SH End Sub '<<============= --- Regards, Norman "crowdx42" wrote in message ... Ok, so I just want to copy the name from a worksheet into the cell A1, I need this to work relative across 20 worksheets. Also in the same macro is it possible to delete the last 4 characters in the worksheet name? The worksheet was originally named from the file name and so has .xls at the end of the name. Any help gratefully appreciated. Patrick -- crowdx42 ------------------------------------------------------------------------ crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749 View this thread: http://www.excelforum.com/showthread...hreadid=573657 |
Copy worksheet name into cell A1?
One way is with a UDF:
Public Function WSName(AnyCell As Range) As String With Application .Volatile WSName = .Caller.Parent.Name End With End Function As for the name change: With Worksheets(x) .Name = Left(.Name, Len(.Name) - 4) End With NickHK "crowdx42" wrote in message ... Ok, so I just want to copy the name from a worksheet into the cell A1, I need this to work relative across 20 worksheets. Also in the same macro is it possible to delete the last 4 characters in the worksheet name? The worksheet was originally named from the file name and so has .xls at the end of the name. Any help gratefully appreciated. Patrick -- crowdx42 ------------------------------------------------------------------------ crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749 View this thread: http://www.excelforum.com/showthread...hreadid=573657 |
Copy worksheet name into cell A1?
Norman,
That's what I thought first, but it is not possible to have the 20 sheets named after the WB, as they would conflict. So maybe 1 sheet is called that but not the other 19. In which case, you only need to rename 1 sheet. But depends what the OP means... NickHK "Norman Jones" wrote in message ... Hi Patrick, Try something like: '============= Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Set WB = Workbooks("YourBook.xls") '<<==== CHANGE For Each SH In WB.Worksheets With SH .Name = Left(.Name, Len(.Name) - 4) .Range("A1").Value = .Name End With Next SH End Sub '<<============= --- Regards, Norman "crowdx42" wrote in message ... Ok, so I just want to copy the name from a worksheet into the cell A1, I need this to work relative across 20 worksheets. Also in the same macro is it possible to delete the last 4 characters in the worksheet name? The worksheet was originally named from the file name and so has .xls at the end of the name. Any help gratefully appreciated. Patrick -- crowdx42 ------------------------------------------------------------------------ crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749 View this thread: http://www.excelforum.com/showthread...hreadid=573657 |
Copy worksheet name into cell A1?
Hi Nick,
That's what I thought first, but it is not possible to have the 20 sheets named after the WB, as they would conflict. My suggested code changes the existing sheet names and merely shortens previously valid names. --- Regards, Norman "NickHK" wrote in message ... Norman, That's what I thought first, but it is not possible to have the 20 sheets named after the WB, as they would conflict. So maybe 1 sheet is called that but not the other 19. In which case, you only need to rename 1 sheet. But depends what the OP means... NickHK "Norman Jones" wrote in message ... Hi Patrick, Try something like: '============= Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Set WB = Workbooks("YourBook.xls") '<<==== CHANGE For Each SH In WB.Worksheets With SH .Name = Left(.Name, Len(.Name) - 4) .Range("A1").Value = .Name End With Next SH End Sub '<<============= --- Regards, Norman "crowdx42" wrote in message ... Ok, so I just want to copy the name from a worksheet into the cell A1, I need this to work relative across 20 worksheets. Also in the same macro is it possible to delete the last 4 characters in the worksheet name? The worksheet was originally named from the file name and so has .xls at the end of the name. Any help gratefully appreciated. Patrick -- crowdx42 ------------------------------------------------------------------------ crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749 View this thread: http://www.excelforum.com/showthread...hreadid=573657 |
Copy worksheet name into cell A1?
Hi Nick,
Just to add and to guess at a possible scenario, the OP may have named the sheets in a fashion resembling: '============= Public Sub Tester() Dim SH As Worksheet Dim i As Long Const myPrefix As String = "ABC" For Each SH In ActiveWorkbook.Worksheets i = i + 1 SH.Name = i & ActiveWorkbook.Name Next SH End Sub '<<============= --- Regards, Norman "NickHK" wrote in message ... Norman, That's what I thought first, but it is not possible to have the 20 sheets named after the WB, as they would conflict. So maybe 1 sheet is called that but not the other 19. In which case, you only need to rename 1 sheet. But depends what the OP means... NickHK "Norman Jones" wrote in message ... Hi Patrick, Try something like: '============= Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Set WB = Workbooks("YourBook.xls") '<<==== CHANGE For Each SH In WB.Worksheets With SH .Name = Left(.Name, Len(.Name) - 4) .Range("A1").Value = .Name End With Next SH End Sub '<<============= --- Regards, Norman "crowdx42" wrote in message ... Ok, so I just want to copy the name from a worksheet into the cell A1, I need this to work relative across 20 worksheets. Also in the same macro is it possible to delete the last 4 characters in the worksheet name? The worksheet was originally named from the file name and so has .xls at the end of the name. Any help gratefully appreciated. Patrick -- crowdx42 ------------------------------------------------------------------------ crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749 View this thread: http://www.excelforum.com/showthread...hreadid=573657 |
Copy worksheet name into cell A1?
Ok, just to clarify. The original sheets were opened renamed from their file name and then moved into a master workbook. This is how each sheet has a name with .xls in it. How does the UDF code work? Is it inserted in the view code on the individual sheet tab? I have tried this and it did not work? Help ! :) Patrick -- crowdx42 ------------------------------------------------------------------------ crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749 View this thread: http://www.excelforum.com/showthread...hreadid=573657 |
Copy worksheet name into cell A1?
Hi Patrick,
Ok, just to clarify. The original sheets were opened renamed from their file name and then moved into a master workbook. This is how each sheet has a name with .xls in it. How does the UDF code work? Is it inserted in the view code on the individual sheet tab? I have tried this and it did not work? Copy the code into a standard module: Alt-F11 to open the VBE Insert Module Paste the suggested code. To run the macro from Excel: Alt-F8 to open the Macro dialog Select 'Tester' (or your replacement name) Run -- Regards, Norman |
All times are GMT +1. The time now is 04:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com