Urgent help: Creating new sheets with template included

[Vaughan.jamie]

Hi all

i am trying to create new sheets from a list but i also want to insert a template into these new sheets

i have created the template on another sheet and i am trying to copy and paste it to the new sheets whilst they are being created

this is my code so far

Sub Auto_Quotes()
    Dim MyCell As Range, MyRange As Range
    
    Set MyRange = Sheets("Summary").Range("A2")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))

    For Each MyCell In MyRange
        Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
        Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
        Worksheets("Template").Rows().Copy 
        Worksheets("Template").Select           'copies template
        Worksheets("Sheets.count").Rows().Select
        Worksheets("Sheets.count").Paste
    Next MyCell
End Sub

It gets to the point where it copys the template to the clipboard but then fails and will not paste it into the newly created sheet

then after i have created this macro i will be wanting some help with another code that transfers the data over from the summary to the relevant places on the template

hope this all makes some sense to you all

i am still learning but honestly enjoying it as once you succeed it feels great
please find example at the bottom

cheers

[ARowbot]

Would it be easier to make a copy of the template spreadsheet rather than add a new sheet and copy the contents of it?

[Vaughan.jamie]

I'm not sure this is where im stuck and need the help with

[Vaughan.jamie]

How would i make a copy of the template sheet and make it rename itself from the list on the summary

[ARowbot]

Try this:

Sub Auto_Quotes()
    Dim MyCell As Range, MyRange As Range
    
    Set MyRange = Sheets("Summary").Range("A2")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))

    For Each MyCell In MyRange
        Sheets("Template").Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = MyCell.Value
    Next MyCell
End Sub

I am Imagining the next part is to fill in the information automatically too...If you need help, then let me know

ALSO: in future make sure any sheets you upload are anonymous!

[Vaughan.jamie]

Hi

Your a legend that works perfectly

how do i make attachments anon?

[ARowbot]

No problem. And I meant by removing the schools names! Now that it is solved please remove the attachment and mark the thread as solved by editing your original post!

ARowbot

[Vaughan.jamie]

thanks so much ARowbot

[ARowbot]

I've done it slightly differently to what you proposed but it should have the same effect!!

P.S Don't edit posts if you ask somebody to do something more also...Makes me look silly!

[Vaughan.jamie]

OMG

ARowbot

is a Legend that is exactly what i needed

cheers

[Vaughan.jamie]

Hi ARowbot

i was just playing around with your formula which is great by the way and works perfectly

But i noticed if i add another school/client after i have saved and closed it then run the macro again it fails because it starts from the beginning again would i need to write a different macro that does what the other one does but doesn't try to duplicate the originals

and i notice aswell with it if i change a price in the file then try to run again to update it does the same as above is there a way round this??

cheers