Copy tab ? times

[JohnUK]

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

 

[sebastienm]

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

 

[Ren]

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]

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

 

[JohnUK]

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

 

[sebastienm]

To run it do something like:

Sub Test()
CopyTabN ActiveSheet, 3 ''' run CopyTabN on active sheet and copy 3
times
End Sub

 

[Sajit]

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,

 

[JohnUK]

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

 

[JohnUK]

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