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
200 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
  • 3
  • 3
7 Comments
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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 21

Expert Comment

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

Author Comment

by:wdabbs
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 50

Assisted Solution

by:teylyn
teylyn earned 500 total points
Comment Utility
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
Comment Utility
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:
teylyn earned 500 total points
Comment Utility
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
Comment Utility
That's okay; it's close enough for what I need. Thank you!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

762 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now