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
244 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
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 23

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) 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 (Microsoft MVP / EE MVE) 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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

627 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