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