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

Help with Sheet Name Please
 

see if this helps.

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 = "Master" & i
Next
Application.ScreenUpdating = True
End Sub

--
Don Guillett
SalesAid Software

"Paul Black" wrote in message
oups.com...
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

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

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

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

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

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

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

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

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

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

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

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

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