Excel to auto generate pages and copy the template of Page 1

[baash]

Hi
I have got a work book in page 1 I have a summary sheet (Lists of names) from which I have formulated it to other pages for cells fill up. So they all have a sample template i.e 1 page. i am trying that to automate like look at the lists and if it needs more than one page to fill up.Auto-generate the pages and copy the template with the the formula in them.
I have tried the code below but I am novice in VBA.


Any help would be appreciated.

[COLOR=#00007F][FONT=Courier]Sub[/FONT][/COLOR][COLOR=#333333][FONT=Courier] CreateAndNameWorksheets()[/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier]Dim[/FONT][/COLOR][COLOR=#333333][FONT=Courier] c [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]As[/FONT][/COLOR][COLOR=#333333][FONT=Courier] Range
[/FONT][/COLOR]                 

[COLOR=#333333][FONT=Courier]    Application.ScreenUpdating = [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]False[/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier]For[/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Each[/FONT][/COLOR][COLOR=#333333][FONT=Courier] c [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]In[/FONT][/COLOR][COLOR=#333333][FONT=Courier] Sheets("Summary").Range("C5:lRow")[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]        Sheets("Template").Copy After:=Sheets(Sheets.Count)[/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier]With[/FONT][/COLOR][COLOR=#333333][FONT=Courier] c[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]            ActiveSheet.Name = .Value[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]            .Parent.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:= _[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]                "'" & .Text & "'!A1", TextToDisplay:=.Text[/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier]End[/FONT][/COLOR][COLOR=#00007F][FONT=Courier]With[/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier]Next[/FONT][/COLOR][COLOR=#333333][FONT=Courier] c[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]    Application.ScreenUpdating = [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]True[/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier]End[/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Sub[/FONT][/COLOR]

Any help would be appreciated

 

[JLGWhiz]

In Excel, 'pages' refer to bolcks of data that will output on a printer as a 'page'. The tabbed segments in a workbook are referred to as sheets. Do you mean sheets or pages?

Sub CreateAndNameWorksheets()
Dim c As Range, lRow As Long
[COLOR=#FF0000]lRow = Cells(Rows.Count, 3).End(xlUp).Row[/COLOR]
Application.ScreenUpdating = False
    For Each c In Sheets("Summary").Range("C5:[COLOR=#FF0000]C" & lRow)[/COLOR]
        Sheets("Template").Copy After:=Sheets(Sheets.Count)
        With c
            ActiveSheet.Name = .Value
            .Parent.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:= _
            "'" & .Text & "'!A1", TextToDisplay:=.Text
        EndWith
    Next c
Application.ScreenUpdating = True
EndSub

 

[baash]

Hi
I meant pages.Sorry for the late reply