Hi, I am after a short piece of code that can copy a sheet/tab, amount to be
determined by a number in a given cell, and rename the tabs. For example: If
the page that I want copied had the number 50 in Cell A1, I want the tab to
be copied 50 times and numbered sequentially (1 to 50)
Is this possible
Many thanks for help
John

Hi ,
try

''' --------------------------
Sub CopyTabN(Wsh as Worksheet, N as long)
Dim wshTo As Worksheet
Dim i As Long

Set wshTo = wsh
For i = 1 To n
wsh.Copy After:=wshTo
Set wshTo = wshTo.Parent.Worksheets(wshTo.Index + 1)
wshTo.Name = wsh.Name & " " & i
Next

End Sub
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"JohnUK" wrote:

> Hi, I am after a short piece of code that can copy a sheet/tab, amount to be
> determined by a number in a given cell, and rename the tabs. For example: If
> the page that I want copied had the number 50 in Cell A1, I want the tab to
> be copied 50 times and numbered sequentially (1 to 50)
> Is this possible
> Many thanks for help
> John
>

You can try the code below. i originally wrote this code to rename the sheets
to predetermined text. I think it should work with ActiveSheet.Name = intI.
If it doesn't, just use text strings.


Dim numTab = number of copies to make
Dim intI = 1
Do While intI < numTab
Sheets("tab to copy").Select
Sheets("tab to copy").Copy After:=Sheets(n + intI-1)
ActiveSheet.Name = intI
intI = intI + 1

Loop

"JohnUK" wrote:

> Hi, I am after a short piece of code that can copy a sheet/tab, amount to be
> determined by a number in a given cell, and rename the tabs. For example: If
> the page that I want copied had the number 50 in Cell A1, I want the tab to
> be copied 50 times and numbered sequentially (1 to 50)
> Is this possible
> Many thanks for help
> John
>

Sorry sebastienm, I cant get this to work. Is this the whole code?

"sebastienm" wrote:

> Hi ,
> try
>
> ''' --------------------------
> Sub CopyTabN(Wsh as Worksheet, N as long)
> Dim wshTo As Worksheet
> Dim i As Long
>
> Set wshTo = wsh
> For i = 1 To n
> wsh.Copy After:=wshTo
> Set wshTo = wshTo.Parent.Worksheets(wshTo.Index + 1)
> wshTo.Name = wsh.Name & " " & i
> Next
>
> End Sub
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "JohnUK" wrote:
>
> > Hi, I am after a short piece of code that can copy a sheet/tab, amount to be
> > determined by a number in a given cell, and rename the tabs. For example: If
> > the page that I want copied had the number 50 in Cell A1, I want the tab to
> > be copied 50 times and numbered sequentially (1 to 50)
> > Is this possible
> > Many thanks for help
> > John
> >

Hi Ren, I cant get this to work either. Am I missing something?

"Ren" wrote:

> You can try the code below. i originally wrote this code to rename the sheets
> to predetermined text. I think it should work with ActiveSheet.Name = intI.
> If it doesn't, just use text strings.
>
>
> Dim numTab = number of copies to make
> Dim intI = 1
> Do While intI < numTab
> Sheets("tab to copy").Select
> Sheets("tab to copy").Copy After:=Sheets(n + intI-1)
> ActiveSheet.Name = intI
> intI = intI + 1
>
> Loop
>
> "JohnUK" wrote:
>
> > Hi, I am after a short piece of code that can copy a sheet/tab, amount to be
> > determined by a number in a given cell, and rename the tabs. For example: If
> > the page that I want copied had the number 50 in Cell A1, I want the tab to
> > be copied 50 times and numbered sequentially (1 to 50)
> > Is this possible
> > Many thanks for help
> > John
> >

To run it do something like:

Sub Test()
CopyTabN ActiveSheet, 3 ''' run CopyTabN on active sheet and copy 3
times
End Sub
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"JohnUK" wrote:

> Sorry sebastienm, I cant get this to work. Is this the whole code?
>
> "sebastienm" wrote:
>
> > Hi ,
> > try
> >
> > ''' --------------------------
> > Sub CopyTabN(Wsh as Worksheet, N as long)
> > Dim wshTo As Worksheet
> > Dim i As Long
> >
> > Set wshTo = wsh
> > For i = 1 To n
> > wsh.Copy After:=wshTo
> > Set wshTo = wshTo.Parent.Worksheets(wshTo.Index + 1)
> > wshTo.Name = wsh.Name & " " & i
> > Next
> >
> > End Sub
> > --
> > Regards,
> > Sébastien
> > <http://www.ondemandanalysis.com>
> >
> >
> > "JohnUK" wrote:
> >
> > > Hi, I am after a short piece of code that can copy a sheet/tab, amount to be
> > > determined by a number in a given cell, and rename the tabs. For example: If
> > > the page that I want copied had the number 50 in Cell A1, I want the tab to
> > > be copied 50 times and numbered sequentially (1 to 50)
> > > Is this possible
> > > Many thanks for help
> > > John
> > >

I had done this myself by this,

Sub app_shts()

'This will add the sheets for each row (page) of the data sheet
'Rpt_WkBk = "Butterfly_221107_09.xls"
'Dim lcol_del As Integer

Windows(main_WkBk).Activate

If row_num = 2 Then 'for first row of data1 (row 2) sheet is copied after
sheet1
Sheets("Template").Copy After:=Workbooks(Rpt_WkBk).Sheets("sheet3")
Else
Sheets("Template").Copy
After:=Workbooks(Rpt_WkBk).Sheets(Trim(page_prev)) 'for subsequent sheets it
is copied after the page_prev
End If

'page_prev = page_num 'set page_prev for the next page
Windows(Rpt_WkBk).Activate
Worksheets("template").Select
Worksheets("template").Name = RTrim(page_num) 'renames copied sheet with
page_num
page_prev = page_num 'set page_prev for the next page

'rng = "r1c" & st_row & ":r" & (st_col - 4) & "c" & end_row

Range(Cells(st_row, 1), Cells(end_row, (st_col - 4))).Select
Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'delete the data in the data area, in preparation to format the new sheet
With Workbooks(Rpt_WkBk).Worksheets(page_num)
lcol_del = .Range("Val_type").Column 'last column for delete
.Range(Cells(st_row, st_col - 1), Cells(end_row, (lcol_del))).Select
End With
Selection.Delete

Call fmat_rpt 'autoformat the report


End Sub

In there, the first row of data should be understood as the first sheet is
copied after the sheet3 that is already there after the workbook is created.

I have a named range called "Val_type" at Q6. I want to make a selection of
a range that includes this range name and delete the contents of the cells
within that selection, this is what I do,

Why does it give me an 'Applicaton defined or object defined error'

Please help,
--
Sajit
Abu Dhabi

Many thanks Sébastien, it worked fine. Much appreciate your help.
Regards
John


"sebastienm" wrote:

> To run it do something like:
>
> Sub Test()
> CopyTabN ActiveSheet, 3 ''' run CopyTabN on active sheet and copy 3
> times
> End Sub
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "JohnUK" wrote:
>
> > Sorry sebastienm, I cant get this to work. Is this the whole code?
> >
> > "sebastienm" wrote:
> >
> > > Hi ,
> > > try
> > >
> > > ''' --------------------------
> > > Sub CopyTabN(Wsh as Worksheet, N as long)
> > > Dim wshTo As Worksheet
> > > Dim i As Long
> > >
> > > Set wshTo = wsh
> > > For i = 1 To n
> > > wsh.Copy After:=wshTo
> > > Set wshTo = wshTo.Parent.Worksheets(wshTo.Index + 1)
> > > wshTo.Name = wsh.Name & " " & i
> > > Next
> > >
> > > End Sub
> > > --
> > > Regards,
> > > Sébastien
> > > <http://www.ondemandanalysis.com>
> > >
> > >
> > > "JohnUK" wrote:
> > >
> > > > Hi, I am after a short piece of code that can copy a sheet/tab, amount to be
> > > > determined by a number in a given cell, and rename the tabs. For example: If
> > > > the page that I want copied had the number 50 in Cell A1, I want the tab to
> > > > be copied 50 times and numbered sequentially (1 to 50)
> > > > Is this possible
> > > > Many thanks for help
> > > > John
> > > >

Many thanks Sajit, I will try out your code next week and will let you know
how I get on
Regards
John


"Sajit" wrote:

> I had done this myself by this,
>
> Sub app_shts()
>
> 'This will add the sheets for each row (page) of the data sheet
> 'Rpt_WkBk = "Butterfly_221107_09.xls"
> 'Dim lcol_del As Integer
>
> Windows(main_WkBk).Activate
>
> If row_num = 2 Then 'for first row of data1 (row 2) sheet is copied after
> sheet1
> Sheets("Template").Copy After:=Workbooks(Rpt_WkBk).Sheets("sheet3")
> Else
> Sheets("Template").Copy
> After:=Workbooks(Rpt_WkBk).Sheets(Trim(page_prev)) 'for subsequent sheets it
> is copied after the page_prev
> End If
>
> 'page_prev = page_num 'set page_prev for the next page
> Windows(Rpt_WkBk).Activate
> Worksheets("template").Select
> Worksheets("template").Name = RTrim(page_num) 'renames copied sheet with
> page_num
> page_prev = page_num 'set page_prev for the next page
>
> 'rng = "r1c" & st_row & ":r" & (st_col - 4) & "c" & end_row
>
> Range(Cells(st_row, 1), Cells(end_row, (st_col - 4))).Select
> Selection.Copy
>
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
>
> 'delete the data in the data area, in preparation to format the new sheet
> With Workbooks(Rpt_WkBk).Worksheets(page_num)
> lcol_del = .Range("Val_type").Column 'last column for delete
> .Range(Cells(st_row, st_col - 1), Cells(end_row, (lcol_del))).Select
> End With
> Selection.Delete
>
> Call fmat_rpt 'autoformat the report
>
>
> End Sub
>
> In there, the first row of data should be understood as the first sheet is
> copied after the sheet3 that is already there after the workbook is created.
>
> I have a named range called "Val_type" at Q6. I want to make a selection of
> a range that includes this range name and delete the contents of the cells
> within that selection, this is what I do,
>
> Why does it give me an 'Applicaton defined or object defined error'
>
> Please help,
> --
> Sajit
> Abu Dhabi
>
>
>