macro - to create new sheets from a data validation list

SS2013 · Sep 11th 2017

Hi

I have a master worksheet where the user can select a customer name and the respective product and price will be displayed. I have used formulas which pull data from another worksheet.

There are more than 50 customers (as a DATA VALIDATION LIST). When a user selects customer A, the relevant product and price get displayed. Is there a way to create 50 sheets using a macro for all the customers listed in the Master Sheet along with the template.

E.g.,

Customer A

Product A Price
Product B Price
Product C Price
Product D Price

TheGlovner · Sep 11th 2017

Re: macro - to create new sheets from a data validation list

So you have 50 customers in a range (which you use to prompt the dropdown options) and you want to use each customer to create a single sheet per customer.

Is that correct?

Is there are good reason for creating 50 spreadsheets? I'm a little confused on the purpose.

SS2013 · Sep 12th 2017

Re: macro - to create new sheets from a data validation list

Quote from TheGlovner;799900

So you have 50 customers in a range (which you use to prompt the dropdown options) and you want to use each customer to create a single sheet per customer.

Is that correct?

Is there are good reason for creating 50 spreadsheets? I'm a little confused on the purpose.

Yes, you are right!

I want each customer in a single sheet. i.e., 50 sheets Currently it is in a drop down menu and the user has to select each customer to display the product information. It would be easy for the user if there are separate sheets for each customer as they can print the sheets, process the payments and also file the sheets for future reference.

Thanks!

TheGlovner · Sep 12th 2017

Re: macro - to create new sheets from a data validation list

This should create the sheets and name them for you, unclear what you want to do from there, I assume you want some data taken from other sheets and added to them.

Code

Option Explicit
Public Sub CreateCustomerSheets()
Dim varWrkCell  As Variant
 Dim wsTemp      As Worksheet
Dim rngCustomer As Range: Set rngCustomer = wsSourceData.Range("Source_Customer_List")
For Each varWrkCell In rngCustomer
     Set wsTemp = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsTemp.Name = varWrkCell.Value2
Next varWrkCell


'Clean Up Objects After Use
set varWrkCell = Nothing
set wsTemp = Nothing
set rngCustomer = Nothing


End Sub

Display More

Couple of objects/references would need renamed in your project for it to work:

"wsSourceData" is the code name I've given to the sheet where my test data was to make sure it worked.
"Source_Customer_List" is the named range on the wsSourceData worksheet where my test Customer Names were held.

Hopefully that gets you started.

macro - to create new sheets from a data validation list

Participate now!

Share