Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Sheet Name Please
Hi Everyone,
What I would like to do is Insert 100 WorkSheets in a WorkBook. The WorkSheets should be Named 1 to 100. I then want to Copy the WorkSheet "Master" ( Inside the Same WorkBook ) to EACH of the Sheets. I would like Cell "A1" of EACH WorkSheet to Contain the Sheet Name ( 1 to 100 ). I have Come up with the Following Code that does Not Work Properly. Option Explicit Option Base 1 Sub Test() Dim i As Integer Application.ScreenUpdating = False For i = 1 To 100 Sheets.Add Sheets("Master").Select Cells.Select Selection.Copy Sheets(i).Paste Next i Application.ScreenUpdating = True End Sub Any Help will be Greatly Appreciated. Thanks in Advance. All the Best. Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Sheet Name Please
Thanks for the Reply Don,
The WorkSheets are Being Named Master. I Just want the WorkSheets to be Numbered 1 to 100. Also, the WorkSheet Name is Not Appearing in Cell "A1" of EACH WorkSheet. Thanks Again. All the Best. Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Sheet Name Please
Hi Paul,
Hi Paul, Would suggest you at least name them with 3 digits so that you could sort your worksheets at a later time. http://www.mvps.org/dmcritchie/excel...toc.htm#sortws as worksheets tend to get rearranged. The following instruction in Don's macro is renaming the copied sheet with a name of "Master (2)" to "Master1", and then "Master2", ...etc... Would suggest something along the lines of Sheets("Master (2)").Name = "D" & Format(i, "000") or Sheets("Master (2)").Name = Format(i, "000") but for what you asked for you would use Sheets("Master (2)").Name = i -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Paul Black" wrote in message oups.com... Thanks for the Reply Don, The WorkSheets are Being Named Master. I Just want the WorkSheets to be Numbered 1 to 100. Also, the WorkSheet Name is Not Appearing in Cell "A1" of EACH WorkSheet. Thanks Again. All the Best. Paul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Sheet Name Please
Thanks for the Reply David,
How would I get the WorkSheet Name into Cell "A1" of EVERY Added WorkSheet as a Value Please. Thanks Again. All the Best. Paul |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Sheet Name Please
Hi David,
I have Tried to get the WorkSheet Name in Cell "A1" of EVERY WorkSheet Using the Following, Unfortunately it does Not Work. Thanks in Advance. Sub Test1() Dim i As Integer Dim myFormula As String Application.ScreenUpdating = False Application.Calculation = xlCalculationManual myFormula = "=MID(CELL("Filename",A1), FIND("]", CELL("Filename",A1))+1,255)" For i = 1 To 5 Sheets("Master").Copy After:=Sheets(Sheets.Count) Sheets("Master (2)").Name = Format(i, "0000") Range("A1") = myFormula Next Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub All the Best. Paul |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Sheet Name Please
New one
Sub Test() Dim i As Integer Application.ScreenUpdating = False For i = 1 To 100 Sheets("Master").Copy After:=Sheets(Sheets.Count) Sheets("Master (2)").Name = i ActiveSheet.Range("a1") = i Next Application.ScreenUpdating = True End Sub -- Don Guillett SalesAid Software "Paul Black" wrote in message oups.com... Thanks for the Reply Don, The WorkSheets are Being Named Master. I Just want the WorkSheets to be Numbered 1 to 100. Also, the WorkSheet Name is Not Appearing in Cell "A1" of EACH WorkSheet. Thanks Again. All the Best. Paul |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Sheet Name Please
One way:
Private Sub Workbook_NewSheet(ByVal Sh As Object) Sh.Cells(1, 1).Value = Sh.Name End Sub In article . com, "Paul Black" wrote: Thanks for the Reply David, How would I get the WorkSheet Name into Cell "A1" of EVERY Added WorkSheet as a Value Please. Thanks Again. All the Best. Paul |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Sheet Name Please
Forget all the extra coding, you already have the sheetname
that you are assigning, so you just need one more line of code. Sub Test() Dim i As Integer Application.ScreenUpdating = False For i = 1 To 10 Sheets("Master").Copy After:=Sheets(Sheets.Count) Sheets("Master (2)").Name = Format(i, "0000") ActiveSheet.Range("A1") = "'" & Format(i, "0000") Next Application.ScreenUpdating = True End Sub If you are not careful your sheetnames of all digits might still get you into trouble later on. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Paul Black" wrote in message I have Tried to get the WorkSheet Name in Cell "A1" of EVERY WorkSheet |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Sheet Name Please
Thanks to Everyone for the Replies.
It is now Working OK Using Dons Code. Thanks Again. All the Best. Paul Don Guillett wrote: New one Sub Test() Dim i As Integer Application.ScreenUpdating = False For i = 1 To 100 Sheets("Master").Copy After:=Sheets(Sheets.Count) Sheets("Master (2)").Name = i ActiveSheet.Range("a1") = i Next Application.ScreenUpdating = True End Sub -- Don Guillett SalesAid Software "Paul Black" wrote in message oups.com... Thanks for the Reply Don, The WorkSheets are Being Named Master. I Just want the WorkSheets to be Numbered 1 to 100. Also, the WorkSheet Name is Not Appearing in Cell "A1" of EACH WorkSheet. Thanks Again. All the Best. Paul |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Sheet Name Please
This is Off Topic, but.... I can't find any HELP re "HOW TO POST" How can I submit a fresh question? -- my911 ------------------------------------------------------------------------ my911's Profile: http://www.excelforum.com/member.php...o&userid=26733 View this thread: http://www.excelforum.com/showthread...hreadid=399836 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Sheet Name Please
Sorry, I think I just found it under Forum Tools -- my911 ------------------------------------------------------------------------ my911's Profile: http://www.excelforum.com/member.php...o&userid=26733 View this thread: http://www.excelforum.com/showthread...hreadid=399836 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Sheet Name Please
You may want to incorporate Davids three digit.
-- Don Guillett SalesAid Software "Paul Black" wrote in message oups.com... Thanks to Everyone for the Replies. It is now Working OK Using Dons Code. Thanks Again. All the Best. Paul Don Guillett wrote: New one Sub Test() Dim i As Integer Application.ScreenUpdating = False For i = 1 To 100 Sheets("Master").Copy After:=Sheets(Sheets.Count) Sheets("Master (2)").Name = i ActiveSheet.Range("a1") = i Next Application.ScreenUpdating = True End Sub -- Don Guillett SalesAid Software "Paul Black" wrote in message oups.com... Thanks for the Reply Don, The WorkSheets are Being Named Master. I Just want the WorkSheets to be Numbered 1 to 100. Also, the WorkSheet Name is Not Appearing in Cell "A1" of EACH WorkSheet. Thanks Again. All the Best. Paul |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Sheet Name Please
Thanks David it Works Great.
Thanks Again to Everyone for the Replies, it is Appreciated. All the Best. Paul David McRitchie wrote: Forget all the extra coding, you already have the sheetname that you are assigning, so you just need one more line of code. Sub Test() Dim i As Integer Application.ScreenUpdating = False For i = 1 To 10 Sheets("Master").Copy After:=Sheets(Sheets.Count) Sheets("Master (2)").Name = Format(i, "0000") ActiveSheet.Range("A1") = "'" & Format(i, "0000") Next Application.ScreenUpdating = True End Sub If you are not careful your sheetnames of all digits might still get you into trouble later on. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Paul Black" wrote in message I have Tried to get the WorkSheet Name in Cell "A1" of EVERY WorkSheet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel sheet bootom half sheet goes behind top part of sheet | Excel Worksheet Functions | |||
Duplicate sheet, autonumber sheet, record data on another sheet | Excel Worksheet Functions | |||
How do I select price from sheet.b where sheet.a part no = sheet.b | Excel Worksheet Functions | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) | |||
Inserting a row in sheet A should Insert a row in sheet B, removing a row in Sheet A should remove the corresponding row in sheet B | Excel Programming |