Add Sheets and rename

G

GregR

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
 
G

Gordon

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

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.
Click to expand...

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
Click to expand...

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

HTH
 
G

GregR

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

Greg
 
G

Gordon

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

Greg
Click to expand...

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.
 
M

Myrna Larson

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

Myrna Larson

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

GregR

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
 
G

Gord Dibben

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
 
G

GregR

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

Greg
 
N

Nemesis_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
 
P

Peo Sjoblom

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
 
N

Nemesis_uk

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
 
T

Tom Ogilvy

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
 
Top