Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy cell from one worksheet to another worksheet in exel | Excel Discussion (Misc queries) | |||
copy data in a cell from worksheet A to worksheet B | Excel Discussion (Misc queries) | |||
How do I copy a date in a worksheet cell to another worksheet? | Excel Worksheet Functions | |||
How do I copy a cell from one worksheet to another worksheet | Excel Discussion (Misc queries) | |||
How to copy formatting when moving from cell to variable to another cell on another worksheet | Excel Programming |