Solved

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

Posted on 2014-01-28
7
237 Views
Last Modified: 2014-02-12
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.
0
Comment
Question by:wdabbs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 39817177
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
 
LVL 22

Expert Comment

by:yo_bee
ID: 39817578
There are a couple of ways.
Cell A1 | Cell B1  | Cell C1
1:   = A1 & " " & B1
2: =CONCATENATE(A1," ", B1)
0
 

Author Comment

by:wdabbs
ID: 39817986
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst
Ingeborg Hawighorst earned 500 total points
ID: 39819533
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
 

Author Comment

by:wdabbs
ID: 39832536
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
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 39834077
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
 

Author Comment

by:wdabbs
ID: 39853378
That's okay; it's close enough for what I need. Thank you!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question