Add Sheets and rename

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>
>>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" <[email protected]> wrote:

>Gordon, thank you for the response. I was looking for script to do the grunt
>work. Thank you.
>
>Greg
>"Gordon" <[email protected]> wrote in message
>news:[email protected]...
>> 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

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" <[email protected]> wrote:

>Gordon, thank you for the response. I was looking for script to do the grunt
>work. Thank you.
>
>Greg
>"Gordon" <[email protected]> wrote in message
>news:[email protected]...
>> 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
news:[email protected]...
> 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" <[email protected]> wrote:
>
> >Gordon, thank you for the response. I was looking for script to do the
grunt
> >work. Thank you.
> >
> >Greg
> >"Gordon" <[email protected]> wrote in message
> >news:[email protected]...
> >> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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 <[email protected]>
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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>
> 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
>