Auto duplicating a sheet

I have just completed working on a sheet and now need to make copies of it. I
know I can right click on the sheet tab and select copy however I need 42
sheets. Is there a way to program XL to create 42 copies of the worksheet. I
am not real familuar with VBE but with help can work through it.

Thanks

Hi

yes, this can be done via VBA, however, if you'ld like to let us know how
you would like your 42 sheets named we can incorporate this in the code.

Cheers
JulieD


"dvonj" wrote:

> I have just completed working on a sheet and now need to make copies of it. I
> know I can right click on the sheet tab and select copy however I need 42
> sheets. Is there a way to program XL to create 42 copies of the worksheet. I
> am not real familuar with VBE but with help can work through it.
>
> Thanks

Hi

okay modifying Jim's code - to give sheet names as well
Public Sub Duplicate()
Dim wks As Worksheet
Dim intCounter As Integer

j = DateValue("26/8/05")
Set wks = ActiveSheet
For intCounter = 1 To 42
wks.Copy , wks
j = j + 7
k = j + 7
ActiveSheet.Name = Format(j, "mmm dd") & " - " & Format(k, "mmm dd")
Next intCounter
End Sub

----
to use the code, right mouse click on the sheet tab - choose view code,
choose insert / module from the menu & copy & paste the code to the right
hand side of the screen. Switch back to your workbook and choose Tools /
Macro / Macros - Duplicate and press the RUN button.
(Please try this on a copy of your workbook first)

Regards
JulieD

"dvonj" wrote:

> That can be a bit confusing but I'll try to explain. I have been naming the
> sheets to represent the drivers work week which starts on Thurs and ends the
> following Wed. So for example Mar 3 - Mar 9 would be one sheet then Mar 10 -
> Mar 16 would be the next and so on. Our weeks are based on the school
> calendar week starting with the first day of school Sep 2 - Sep 8.
> Hope this makes sence.
>
> "JulieD" wrote:
>
> > Hi
> >
> > yes, this can be done via VBA, however, if you'ld like to let us know how
> > you would like your 42 sheets named we can incorporate this in the code.
> >
> > Cheers
> > JulieD
> >
> >
> > "dvonj" wrote:
> >
> > > I have just completed working on a sheet and now need to make copies of it. I
> > > know I can right click on the sheet tab and select copy however I need 42
> > > sheets. Is there a way to program XL to create 42 copies of the worksheet. I
> > > am not real familuar with VBE but with help can work through it.
> > >
> > > Thanks

Ok JulieD and Jim that was awsome. Thanks for the help.

"JulieD" wrote:

> Hi
>
> okay modifying Jim's code - to give sheet names as well
> Public Sub Duplicate()
> Dim wks As Worksheet
> Dim intCounter As Integer
>
> j = DateValue("26/8/05")
> Set wks = ActiveSheet
> For intCounter = 1 To 42
> wks.Copy , wks
> j = j + 7
> k = j + 7
> ActiveSheet.Name = Format(j, "mmm dd") & " - " & Format(k, "mmm dd")
> Next intCounter
> End Sub
>
> ----
> to use the code, right mouse click on the sheet tab - choose view code,
> choose insert / module from the menu & copy & paste the code to the right
> hand side of the screen. Switch back to your workbook and choose Tools /
> Macro / Macros - Duplicate and press the RUN button.
> (Please try this on a copy of your workbook first)
>
> Regards
> JulieD
>
> "dvonj" wrote:
>
> > That can be a bit confusing but I'll try to explain. I have been naming the
> > sheets to represent the drivers work week which starts on Thurs and ends the
> > following Wed. So for example Mar 3 - Mar 9 would be one sheet then Mar 10 -
> > Mar 16 would be the next and so on. Our weeks are based on the school
> > calendar week starting with the first day of school Sep 2 - Sep 8.
> > Hope this makes sence.
> >
> > "JulieD" wrote:
> >
> > > Hi
> > >
> > > yes, this can be done via VBA, however, if you'ld like to let us know how
> > > you would like your 42 sheets named we can incorporate this in the code.
> > >
> > > Cheers
> > > JulieD
> > >
> > >
> > > "dvonj" wrote:
> > >
> > > > I have just completed working on a sheet and now need to make copies of it. I
> > > > know I can right click on the sheet tab and select copy however I need 42
> > > > sheets. Is there a way to program XL to create 42 copies of the worksheet. I
> > > > am not real familuar with VBE but with help can work through it.
> > > >
> > > > Thanks

you're welcome

"dvonj" <[email protected]> wrote in message
news:[email protected]...
> Ok JulieD and Jim that was awsome. Thanks for the help.
>
> "JulieD" wrote:
>
>> Hi
>>
>> okay modifying Jim's code - to give sheet names as well
>> Public Sub Duplicate()
>> Dim wks As Worksheet
>> Dim intCounter As Integer
>>
>> j = DateValue("26/8/05")
>> Set wks = ActiveSheet
>> For intCounter = 1 To 42
>> wks.Copy , wks
>> j = j + 7
>> k = j + 7
>> ActiveSheet.Name = Format(j, "mmm dd") & " - " & Format(k, "mmm
>> dd")
>> Next intCounter
>> End Sub
>>
>> ----
>> to use the code, right mouse click on the sheet tab - choose view code,
>> choose insert / module from the menu & copy & paste the code to the right
>> hand side of the screen. Switch back to your workbook and choose Tools /
>> Macro / Macros - Duplicate and press the RUN button.
>> (Please try this on a copy of your workbook first)
>>
>> Regards
>> JulieD
>>
>> "dvonj" wrote:
>>
>> > That can be a bit confusing but I'll try to explain. I have been naming
>> > the
>> > sheets to represent the drivers work week which starts on Thurs and
>> > ends the
>> > following Wed. So for example Mar 3 - Mar 9 would be one sheet then Mar
>> > 10 -
>> > Mar 16 would be the next and so on. Our weeks are based on the school
>> > calendar week starting with the first day of school Sep 2 - Sep 8.
>> > Hope this makes sence.
>> >
>> > "JulieD" wrote:
>> >
>> > > Hi
>> > >
>> > > yes, this can be done via VBA, however, if you'ld like to let us know
>> > > how
>> > > you would like your 42 sheets named we can incorporate this in the
>> > > code.
>> > >
>> > > Cheers
>> > > JulieD
>> > >
>> > >
>> > > "dvonj" wrote:
>> > >
>> > > > I have just completed working on a sheet and now need to make
>> > > > copies of it. I
>> > > > know I can right click on the sheet tab and select copy however I
>> > > > need 42
>> > > > sheets. Is there a way to program XL to create 42 copies of the
>> > > > worksheet. I
>> > > > am not real familuar with VBE but with help can work through it.
>> > > >
>> > > > Thanks