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
221 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: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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

770 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