Macro to create new supplier sheets for each new supplier

[richardjshaffer]

Please, hope someone can help.

What we are trying to achieve is a macro which creates a new worksheet for every unique supplier listed in the SUMMARY worksheet.

So the SUMMARY worksheet is populated which lots of data with many different suppliers listed down in column B, and the relevant data in the other columns - what the macro needs to do is use the TEMPLATE worksheet and create a new worksheet for each different supplier (with the worksheet created named by the supplier number) and the relevant fields in each new worksheet filled in.

I have an example file which just shows 1 worksheet created (F1111) for the only supplier mentioned in the SUMMARY worksheet, but there could be many different suppliers - I'm not sure how, how would I attach this file to my thread?

many thanks,

Richard

 

[Akashwani]

Hi Richard,

I don't write or understand VBA so I cannot help you with code, sorry.

I use the following (which probably isn't perfect) to create copies of a Template based on the range of names in B2:B on sheet Lists.....

Sub CreateSheets()
Application.ScreenUpdating = False
    With Sheets("Lists")
        For a = 2 To .Cells(Rows.Count, 2).End(xlUp).Row
            sname = .Cells(a, 2).Value
            destsht = CStr(Format(sname, ""))
        On Error Resume Next
            If Not Worksheets(destsht).Name = destsht Then
                Sheets("Template").Copy After:=Sheets(Sheets.Count)
                Sheets("Template (2)").Name = destsht
            End If
            
        Next
    End With
Application.ScreenUpdating = True
End Sub

Sheet Template needs to be the last sheet tab on the right and all the sheet names created will appear to the right of your Template sheet.

Good luck.

Ak

 

[richardjshaffer]

Thanks for your reply, though I think my example is a little more complex. Is there a way to post my excel example on the site for people to see and hopefully help?

thanks

 

[Akashwani]

Hi Richard,

Take a look here on how to post a sample of your worksheet......

http://www.mrexcel.com/forum/showthread.php?t=89356

You may want to upload a sample of your workbook to somewhere like this....
http://www.4shared.com/?signout=1
someone may take a look at it and give you some help.

Ak

 

[richardjshaffer]

Thank you for your advice, I have created the file on the shared space if someone can please help:

http://www.4shared.com/file/4B_nimp5/RTM_Template__RS_v3.html


thanks