I have a workbook with one sheet named Sept. I want to add 11 more sheets,
name them Oct - Aug and copy the contents of the original sheet to the new
sheets. Then change the value in A3 of each sheet to the sheet name. The
name of the initial sheet should be optional. If it was name Jan, the added
sheets would be Feb - Dec. TIA

Greg

GregR wrote:
I have a workbook with one sheet named Sept. I want to add 11 more sheets,
name them Oct - Aug

Now if you were using LOTUS, it would do that for you automatically when
you created the new sheets! as it is, you'll have to do that bit manually.


and copy the contents of the original sheet to the new
sheets.

Select all of the original sheet and copy. Then click on the tab of the
first new sheet, go to the tab of the last new sheet and click on it
while holding down Shift. That should select all the new sheets. Then,
go to the first of the selected sheets and paste the data. That will
paste data in ALL the selected sheets. Unselect the sheets by clicking
back on the tab of the original sheet.

Then change the value in A3 of each sheet to the sheet name. The
name of the initial sheet should be optional. If it was name Jan, the added
sheets would be Feb - Dec. TIA


You'll also have to do this manually, unless you write a VB Script to do
it, AFAIK.

HTH


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

Gordon, thank you for the response. I was looking for script to do the grunt
work. Thank you.

Greg
"Gordon" wrote in message
...
GregR wrote:
I have a workbook with one sheet named Sept. I want to add 11 more
sheets,
name them Oct - Aug

Now if you were using LOTUS, it would do that for you automatically when
you created the new sheets! as it is, you'll have to do that bit manually.


and copy the contents of the original sheet to the new
sheets.

Select all of the original sheet and copy. Then click on the tab of the
first new sheet, go to the tab of the last new sheet and click on it
while holding down Shift. That should select all the new sheets. Then,
go to the first of the selected sheets and paste the data. That will
paste data in ALL the selected sheets. Unselect the sheets by clicking
back on the tab of the original sheet.

Then change the value in A3 of each sheet to the sheet name. The
name of the initial sheet should be optional. If it was name Jan, the
added
sheets would be Feb - Dec. TIA


You'll also have to do this manually, unless you write a VB Script to do
it, AFAIK.

HTH


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

GregR wrote:
Gordon, thank you for the response. I was looking for script to do the grunt
work. Thank you.


Beyond my ken I'm afraid!


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

GregR wrote:
Gordon, thank you for the response. I was looking for script to do the grunt
work. Thank you.

Greg
"Gordon" wrote in message
...

GregR wrote:

I have a workbook with one sheet named Sept. I want to add 11 more

sheets,

name them Oct - Aug

Now if you were using LOTUS, it would do that for you automatically when
you created the new sheets! as it is, you'll have to do that bit manually.



and copy the contents of the original sheet to the new
sheets.

Select all of the original sheet and copy. Then click on the tab of the
first new sheet, go to the tab of the last new sheet and click on it
while holding down Shift. That should select all the new sheets. Then,
go to the first of the selected sheets and paste the data. That will
paste data in ALL the selected sheets. Unselect the sheets by clicking
back on the tab of the original sheet.

Then change the value in A3 of each sheet to the sheet name. The

name of the initial sheet should be optional. If it was name Jan, the

added

sheets would be Feb - Dec. TIA


You'll also have to do this manually, unless you write a VB Script to do
it, AFAIK.

HTH

If this is only a one-off, then you probably would be able to do all
this manually in the time it takes to write a script! If you're going to
do this on a regular basis, then the script is probably the way to go.

--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

Did you post in the Programming news group? That's the place if you want a
macro.

On Wed, 9 Feb 2005 09:51:07 -0800, "GregR" wrote:

Gordon, thank you for the response. I was looking for script to do the grunt
work. Thank you.

Greg
"Gordon" wrote in message
...
GregR wrote:
I have a workbook with one sheet named Sept. I want to add 11 more
sheets,
name them Oct - Aug

Now if you were using LOTUS, it would do that for you automatically when
you created the new sheets! as it is, you'll have to do that bit manually.


and copy the contents of the original sheet to the new
sheets.

Select all of the original sheet and copy. Then click on the tab of the
first new sheet, go to the tab of the last new sheet and click on it
while holding down Shift. That should select all the new sheets. Then,
go to the first of the selected sheets and paste the data. That will
paste data in ALL the selected sheets. Unselect the sheets by clicking
back on the tab of the original sheet.

Then change the value in A3 of each sheet to the sheet name. The
name of the initial sheet should be optional. If it was name Jan, the
added
sheets would be Feb - Dec. TIA


You'll also have to do this manually, unless you write a VB Script to do
it, AFAIK.

HTH


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

Why not Edit/Copy Sheet 11 times? You are still left with the renaming issue.


On Wed, 09 Feb 2005 17:25:31 +0000, Gordon
wrote:

GregR wrote:
I have a workbook with one sheet named Sept. I want to add 11 more sheets,
name them Oct - Aug

Now if you were using LOTUS, it would do that for you automatically when
you created the new sheets! as it is, you'll have to do that bit manually.


and copy the contents of the original sheet to the new
sheets.

Select all of the original sheet and copy. Then click on the tab of the
first new sheet, go to the tab of the last new sheet and click on it
while holding down Shift. That should select all the new sheets. Then,
go to the first of the selected sheets and paste the data. That will
paste data in ALL the selected sheets. Unselect the sheets by clicking
back on the tab of the original sheet.

Then change the value in A3 of each sheet to the sheet name. The
name of the initial sheet should be optional. If it was name Jan, the added
sheets would be Feb - Dec. TIA


You'll also have to do this manually, unless you write a VB Script to do
it, AFAIK.

HTH

Myrna, thanks for the reply. No I haven't posted in the programming group,
as I didn't want to multipost. I could and have copied 11 times, but would
like an automated way. Thanks

Greg
"Myrna Larson" wrote in message
...
Why not Edit/Copy Sheet 11 times? You are still left with the renaming
issue.


On Wed, 09 Feb 2005 17:25:31 +0000, Gordon
wrote:

GregR wrote:
I have a workbook with one sheet named Sept. I want to add 11 more
sheets,
name them Oct - Aug

Now if you were using LOTUS, it would do that for you automatically when
you created the new sheets! as it is, you'll have to do that bit
manually.


and copy the contents of the original sheet to the new
sheets.

Select all of the original sheet and copy. Then click on the tab of the
first new sheet, go to the tab of the last new sheet and click on it
while holding down Shift. That should select all the new sheets. Then,
go to the first of the selected sheets and paste the data. That will
paste data in ALL the selected sheets. Unselect the sheets by clicking
back on the tab of the original sheet.

Then change the value in A3 of each sheet to the sheet name. The
name of the initial sheet should be optional. If it was name Jan, the
added
sheets would be Feb - Dec. TIA


You'll also have to do this manually, unless you write a VB Script to do
it, AFAIK.

HTH

Greg

Sub Add_NameWS()
Dim mycount As Long
With Worksheets("Sheet1")
Range(("A1"), Cells(Rows.Count, 1).End(xlUp)).Select
mycount = Selection.Rows.Count
For i = 1 To mycount
Sheets.Add(Type:="Worksheet").Name = Worksheets("Sheet1") _
.Cells(i, 1).Value
Next i
End With
End Sub

Insert a worksheet named Sheet1.

Enter Oct through Aug in column A1:A11 on Sheet1 then run the macro.

Delete Sheet1 when happy.


Gord Dibben Excel MVP

On Wed, 9 Feb 2005 09:51:07 -0800, "GregR" wrote:

Gordon, thank you for the response. I was looking for script to do the grunt
work. Thank you.

Greg
"Gordon" wrote in message
...
GregR wrote:
I have a workbook with one sheet named Sept. I want to add 11 more
sheets,
name them Oct - Aug

Now if you were using LOTUS, it would do that for you automatically when
you created the new sheets! as it is, you'll have to do that bit manually.


and copy the contents of the original sheet to the new
sheets.

Select all of the original sheet and copy. Then click on the tab of the
first new sheet, go to the tab of the last new sheet and click on it
while holding down Shift. That should select all the new sheets. Then,
go to the first of the selected sheets and paste the data. That will
paste data in ALL the selected sheets. Unselect the sheets by clicking
back on the tab of the original sheet.

Then change the value in A3 of each sheet to the sheet name. The
name of the initial sheet should be optional. If it was name Jan, the
added
sheets would be Feb - Dec. TIA


You'll also have to do this manually, unless you write a VB Script to do
it, AFAIK.

HTH


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

Gord, adds all the sheets, but doesn't copy the contents of the original
sheet to the added sheets. TIA

Greg
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Greg

Sub Add_NameWS()
Dim mycount As Long
With Worksheets("Sheet1")
Range(("A1"), Cells(Rows.Count, 1).End(xlUp)).Select
mycount = Selection.Rows.Count
For i = 1 To mycount
Sheets.Add(Type:="Worksheet").Name = Worksheets("Sheet1") _
.Cells(i, 1).Value
Next i
End With
End Sub

Insert a worksheet named Sheet1.

Enter Oct through Aug in column A1:A11 on Sheet1 then run the macro.

Delete Sheet1 when happy.


Gord Dibben Excel MVP

On Wed, 9 Feb 2005 09:51:07 -0800, "GregR" wrote:

Gordon, thank you for the response. I was looking for script to do the
grunt
work. Thank you.

Greg
"Gordon" wrote in message
...
GregR wrote:
I have a workbook with one sheet named Sept. I want to add 11 more
sheets,
name them Oct - Aug

Now if you were using LOTUS, it would do that for you automatically
when
you created the new sheets! as it is, you'll have to do that bit
manually.


and copy the contents of the original sheet to the new
sheets.

Select all of the original sheet and copy. Then click on the tab of the
first new sheet, go to the tab of the last new sheet and click on it
while holding down Shift. That should select all the new sheets. Then,
go to the first of the selected sheets and paste the data. That will
paste data in ALL the selected sheets. Unselect the sheets by clicking
back on the tab of the original sheet.

Then change the value in A3 of each sheet to the sheet name. The
name of the initial sheet should be optional. If it was name Jan, the
added
sheets would be Feb - Dec. TIA


You'll also have to do this manually, unless you write a VB Script to
do
it, AFAIK.

HTH


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

Greg, very handy little routine, but could you suggest a solution for
this in reverse. i.e sheets already exist with different names, run a
macro to put names of sheets down a1-a11 in sheet 1?

cheers

One way

Sub SheetNames()
Dim wkSht As Worksheet
Range("A1").Select
For Each wkSht In Worksheets
Selection = wkSht.Name
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next wkSht
End Sub


--

Regards,

Peo Sjoblom

"Nemesis_uk" wrote in message
ups.com...
Greg, very handy little routine, but could you suggest a solution for
this in reverse. i.e sheets already exist with different names, run a
macro to put names of sheets down a1-a11 in sheet 1?

cheers

Thanks that's great Peo, I think thats about the 3rd time you come to
my rescue,could I convert those names on sheet1 to lnks to the actual
sheets , that would be really handy with some of the worksheets I'm
currently working on.


Regards

Nemesis_uk

Peo Sjoblom wrote:
One way

Sub SheetNames()
Dim wkSht As Worksheet
Range("A1").Select
For Each wkSht In Worksheets
Selection = wkSht.Name
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next wkSht
End Sub

Sub SheetNames()
Dim wkSht As Worksheet
Range("A1").Select
For Each wkSht In Worksheets
Selection = wkSht.Name
ActiveSheet.Hyperlinks.Add _
Anchor:=Selection, _
Address:="", _
SubAddress:="'" & wkSht.Name & _
"'!A1"
ActiveCell.Offset(rowOffset:=1, _
columnOffset:=0).Activate
Next wkSht
End Sub

--
Regards,
Tom Ogilvy



"Nemesis_uk" wrote in message
oups.com...
Thanks that's great Peo, I think thats about the 3rd time you come to
my rescue,could I convert those names on sheet1 to lnks to the actual
sheets , that would be really handy with some of the worksheets I'm
currently working on.


Regards

Nemesis_uk

Peo Sjoblom wrote:
One way

Sub SheetNames()
Dim wkSht As Worksheet
Range("A1").Select
For Each wkSht In Worksheets
Selection = wkSht.Name
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next wkSht
End Sub