How do I merge a column of data in one worksheet into a template document and create one new worksheet for each merged item?

I have an ordering template I need to use for a vendor, in Excel. I have a list of about 35 customers that each need something ordered on that same template. Rather than save-and-copy the order sheet 35 times, is there a way to "merge" the customer names from one worksheet into the orders document, and create a new .xslx document for each company's order? I'm using the latest version of Excel via Office 365.
wdabbsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello,

you can add one line of code to add the company name into the cells:

After the line

ActiveSheet.Name = cel.Value

Open in new window


add this line

ActiveSheet.Range("H26:H27") = cel.Value

Open in new window


I can run the macro multiple times. What exact error do you get?

cheers, teylyn
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

Please help us understand your issue.

>> I have an ordering template ...

Is that an Excel template as in *.xslt or is it a reguar workbook? Please explain how you use the term "template" and what it means in your scenario.

Can you post a sample file?

Why would you need to create 35 different files for 35 different customers. You could set up a list of customers in a file somewhere. The template file could use a lookup into the customer list.

But all in all, there is just not enough info about your scenario. You need to share your file and explain the business rules.

Before you do that, nobody will be able to help.

cheers, teylyn
0
 
yo_beeDirector of Information TechnologyCommented:
There are a couple of ways.
Cell A1 | Cell B1  | Cell C1
1:   = A1 & " " & B1
2: =CONCATENATE(A1," ", B1)
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
wdabbsAuthor Commented:
When I said template, I meant it in the broad sense, as in a .xlsx file, not .xlst.

For example,

The order sheet (serving as the "template"):

Customer Name |  Product  |  Cost  |  Quantity

1. ABC Co. |  WidgetX  |  $10.00  |  3


The company name sheet (serving as the source file):

1. ABC Co.
2. XYZ Co.
3. LMN Co.
4. DEF Co.


I want the order sheet to create a new worksheet for each company, like a mail merge in MS Word.  I understand I need to do a lookup, but I'm not sure about the mechanics of creating a lookup and creating a new worksheet for each resulting merge. The vendor needs a separate order sheet for each customer. I can fill in the product, cost, quantity later.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Try this:

Option Explicit

Sub CreateSheets()
Dim cel As Range
Dim wsList As Worksheet
Dim wsTemplate As Worksheet
Dim wb As Workbook
Dim i As Integer
Dim SheetExists As Boolean

SheetExists = False
Set wb = ActiveWorkbook
Set wsList = wb.Sheets("List") ' enter the name of your sheet here
Set wsTemplate = wb.Sheets("Template") ' enter the name of your sheet here
For Each cel In wsList.Range("A2:A5") ' adjust the cell range that contains the names
    For i = 1 To wb.Sheets.Count
        If wb.Sheets(i).Name = cel.Value Then
            SheetExists = True
        End If
    Next i
    If Not SheetExists Then
        wsTemplate.Copy after:=wb.Sheets(wb.Sheets.Count)
        ActiveSheet.Name = cel.Value
    End If
    SheetExists = False
Next cel
End Sub

Open in new window


A lot more error checking could be done to ensure that the text in the list of cells makes a valid sheet name, but I have not included that. You can run the macro multiple times. If a sheet name already exists, it will just ignore that cell and not try to create a new sheet with the same name.

cheers, teylyn
0
 
wdabbsAuthor Commented:
Thanks Teylyn,

I'm trying that now. As you predicted, it hits a snag on a name that is too long for a tab, but I can shorten the name.

It's almost there -- is there a way to populate the same tab name in one of the cells of the that worksheet? For example, the newly-generated worksheet named "ACME Co" also needs "ACME Co" in cells H26:H27.

It doesn't like it when I run it multiple times; it balks that there is already a particular name in use. It's okay, I can delete the new tabs and start again.
0
 
wdabbsAuthor Commented:
That's okay; it's close enough for what I need. Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.