Sheet Name to Cell Value
Hi: have a very large workbook with about 200 sheets. Each sheet has a name. I need to copy the sheet name to cell B7 on that sheet for every sheet. Does any one has a code for this? I was tryng to use ActiveSheet.Range("B7").Text = ActiveSheet.Name but I can't get it to work. :confused: -- halem2 ------------------------------------------------------------------------ halem2's Profile: http://www.excelforum.com/member.php...fo&userid=9930 View this thread: http://www.excelforum.com/showthread...hreadid=556975 |
Sheet Name to Cell Value
Hi Halem,
Try: '============= Public Sub Tester() Dim SH As Worksheet For Each SH In ThisWorkbook.Worksheets SH.Range("B7").Value = SH.Name Next SH End Sub '<<============= --- Regards, Norman "halem2" wrote in message ... Hi: have a very large workbook with about 200 sheets. Each sheet has a name. I need to copy the sheet name to cell B7 on that sheet for every sheet. Does any one has a code for this? I was tryng to use ActiveSheet.Range("B7").Text = ActiveSheet.Name but I can't get it to work. :confused: -- halem2 ------------------------------------------------------------------------ halem2's Profile: http://www.excelforum.com/member.php...fo&userid=9930 View this thread: http://www.excelforum.com/showthread...hreadid=556975 |
Sheet Name to Cell Value
Option Explicit
Sub SheetNames() Dim wks As Worksheet For Each wks In Worksheets wks.Range("B7").Value = wks.Name Next wks End Sub Mark halem2 wrote: Hi: have a very large workbook with about 200 sheets. Each sheet has a name. I need to copy the sheet name to cell B7 on that sheet for every sheet. Does any one has a code for this? I was tryng to use ActiveSheet.Range("B7").Text = ActiveSheet.Name but I can't get it to work. :confused: -- halem2 ------------------------------------------------------------------------ halem2's Profile: http://www.excelforum.com/member.php...fo&userid=9930 View this thread: http://www.excelforum.com/showthread...hreadid=556975 |
Sheet Name to Cell Value
Here you go...
sub SheetNames dim wks as worksheet for each wks in worksheets wks.range("B7").value = wks.name next wks end sub -- HTH... Jim Thomlinson "halem2" wrote: Hi: have a very large workbook with about 200 sheets. Each sheet has a name. I need to copy the sheet name to cell B7 on that sheet for every sheet. Does any one has a code for this? I was tryng to use ActiveSheet.Range("B7").Text = ActiveSheet.Name but I can't get it to work. :confused: -- halem2 ------------------------------------------------------------------------ halem2's Profile: http://www.excelforum.com/member.php...fo&userid=9930 View this thread: http://www.excelforum.com/showthread...hreadid=556975 |
Sheet Name to Cell Value
Option Explicit
Sub testme() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets With wks .Range("B7").Value = "'" & .Name End With Next wks End Sub But if the sheets can change name, maybe a formula would be better: Option Explicit Sub testme() Dim wks As Worksheet Dim myFormula As String myFormula _ = "=MID(CELL(""filename"",a1),FIND(""]"",CELL(""filename"",a1))+1,255)" For Each wks In ActiveWorkbook.Worksheets With wks .Range("B7").Formula = myFormula End With Next wks End Sub The formula will reevaluate when the worksheet name changes. (The workbook has to be saved at least once for the formula to work, though.) halem2 wrote: Hi: have a very large workbook with about 200 sheets. Each sheet has a name. I need to copy the sheet name to cell B7 on that sheet for every sheet. Does any one has a code for this? I was tryng to use ActiveSheet.Range("B7").Text = ActiveSheet.Name but I can't get it to work. :confused: -- halem2 ------------------------------------------------------------------------ halem2's Profile: http://www.excelforum.com/member.php...fo&userid=9930 View this thread: http://www.excelforum.com/showthread...hreadid=556975 -- Dave Peterson |
Sheet Name to Cell Value
worked like a charm! thanks a million. :) :) ;) ;) -- halem2 ------------------------------------------------------------------------ halem2's Profile: http://www.excelforum.com/member.php...fo&userid=9930 View this thread: http://www.excelforum.com/showthread...hreadid=556975 |
All times are GMT +1. The time now is 09:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com