Creating new copied worksheets named from range

googlehoff · Dec 13th 2005

I have a master list of 260 different listings. I also have a seperate worksheet that is a form for each of these listings data in a more individualized way. What I am trying to do is create 260 different worksheets that were copied from the form worksheet and named from a list of names on the master. I then want to populate each of those sheets with the corresponding data from the master. Please help. Thanks

chrisxs5 · Dec 13th 2005

Re: Creating new copied worksheets named from range

Are you familiar with Access and if so, have you considered importing your tables and organizing that way? With everything you need to accomplish it might be a good thought.

I am not the quickest at code and it would take me about a day to write what you need, but generally what you will need to do is write code to:
Create a page,
Copy data from your form sheet, paste it
Find matching data from master, copy and paste
Format and name sheet,
Start over using next line of info from form sheet,
Continue till done.

If my understanding is correct you could start collecting snippets of code to do what you need. Just remeber use a backup copy in case it doesnt work out.

So I am done thinking outloud Goodluck!

googlehoff · Dec 13th 2005

Re: Creating new copied worksheets named from range

Thanks Chris.

I was considering using access, but the people that will actually be using the workbook/database are VERY uncomfortable with access.

It seems a little rediculous to have 260 worksheets, but it will save headaches down the line.

Thanks for your outline, I think I will need to approach this step by step rather than trying to get the big picture all at once. Thanks again and if anyone wants to tackle the code, it would be GREATLY appreciated.

chrisxs5 · Dec 13th 2005

Re: Creating new copied worksheets named from range

No problem, if I was about a year more versed in code I'm sure it wouldnt be a problem, but I do it the same way:

Make an outline,
Collect code,
then post on the forum when it doesnt work.

Goodluck.

**norie** · Dec 13th 2005

Re: Creating new copied worksheets named from range

googlehoff

Could you post a sample workbook?

This shouldn't be too difficult to do but it would be helpful to have something to work on.

Anonymous · Dec 13th 2005

Re: Creating new copied worksheets named from range

Per norie, a sample would be useful. Attached is a guess. It creates a copy of sheet "Form" for each entry (used cell) in column A and names one of the new sheets for each cell value. (Play with the attached.)

Code

Sub MakeSheets()
Dim c As Range, OldSht As Worksheet
Dim ListSht As Worksheet, Form As Worksheet


Set ListSht = Sheets("MasterList")
Set Form = Sheets("Form")
Set OldSht = Form
Application.ScreenUpdating = False
For Each c In ListSht.Range(ListSht.Cells(1, 1), ListSht.Cells(Rows.Count, 1).End(xlUp))
        Form.Copy After:=OldSht
        ActiveSheet.Name = c.Value
        Set OldSht = ActiveSheet
Next c
Application.ScreenUpdating = True
End Sub

Display More

googlehoff · Dec 14th 2005

Re: Creating new copied worksheets named from range

Thanks for all of your help so far!

I have attached a rough example of what the workbook looks like.

Again, the goal for the VBA code is to take the individuals listed on the master list and input their data into a copied form named after the client with everything still remaining linked to the original page. Also there are some people with more than one listing and the individual listings need to be on the same page ( I assume a do until loop or something like that)

Any progress on this will be GREATLY appreciated!

Thansk again

**norie** · Dec 14th 2005

Re: Creating new copied worksheets named from range

So you want to copy sheet2, name the new sheet after the client and enter the appropriate data in the new sheet?

I'll take a look at it, but have you not considered using Word Mail Merge for this?

googlehoff · Dec 14th 2005

Re: Creating new copied worksheets named from range

That is essentially what I would like to accomplish.

I have looked at mail merge, but I need these links to remain live so updates to the figures can be made throughout the year.

Also I don't believe I would be able to have multiple entries for the same client with a mail merge, it would be one account per page.

**norie** · Dec 14th 2005

Re: Creating new copied worksheets named from range

Just noticed the multiple entries.:oops:

I think you'll need to explain further what you want to do.

Perhaps you could attach an example of the expected result.

googlehoff · Dec 14th 2005

Re: Creating new copied worksheets named from range

here is an example with a desired final product

googlehoff · Dec 15th 2005

Re: Creating new copied worksheets named from range

Any further help would be greatly appreciated! :rock:

Creating new copied worksheets named from range

Participate now!

Share