Automatically Create New Worksheets Based Upon List Of Names

Aug 10, 2014

I have a list of names in column A, and want to automatically create worksheets within this same workbook based on the list.

List of values:
Dairy NI
Dairy SI
Sheep and Beef
Sports Turf
Water Industry
Horticulture Services
Production Horticulture
Other Agriculture

Therefore, a macro or coding to automatically create 9 spreadsheets named as per this list.... My list is approximately 50+ hence inquiring if there is an easier way!

View 1 Replies


Create List Of Tab Names And Automatically Update When New Added

Mar 13, 2013

Is there a way to automatically create list of the Tab names, and have it automatically update when new tabs are added?

View 2 Replies View Related

Create & Name Worksheets Based On Names From Table

Mar 28, 2008

I'm trying to create a macro which will run through every salesperson in a list, and copy and paste their product and client data (from separate sheets) to a template sheet. The template sheet will then by copied to the Salesgroup workbook, and renamed to the salesperson's name. Then the macro should go back to the original workbook and do it all again for the next salesperson, putting them into the same workbook.

The problem I'm having is that the references to the Template worksheet seem to be being messed up by copying the template sheet to the new workbook, and it then ends up creating all the worksheets, but it stops taking the data from the data sheets, and ends up copying the previous worksheet and pasting that into the new copied template, which means that I get the template multiple times across the same sheet.

Sub SalesSheets()
Dim Salesbook As Workbook
Dim NewBook As Workbook
Dim SalesDataSheet As Worksheet
Dim ClientDataSheet As Worksheet
Dim SalespersonListSheet As Worksheet
Dim Template As Worksheet
Dim rRange As Range
Dim rCell As Range
Dim strText As String
Dim SalesGroup As String
Application.DisplayAlerts = False
Application. ScreenUpdating = False
Set Salesbook = Application.Workbooks("2008 Salesperson TEST.xls") 'change filename here
Set Template = Salesbook.Worksheets("Salesperson Template")
Set SalesDataSheet = Salesbook.Worksheets("SP product YoY")
Set ClientDataSheet = Salesbook.Worksheets("Client YoY")
Set SalespersonListSheet = Salesbook.Worksheets("Salesperson List")..........................

View 3 Replies View Related

Excel 2010 :: Create Master List Worksheet From Lists Of Names On Several Worksheets

Jun 6, 2013

I'm using Excel 2010. I have a workbook that has six or seven worksheets in it. Each worksheet has a header row, and then lists of clients at the clinic where I work. The columns are last name, first name, medicaid #, social security #, DOB. We use these lists every month to verify that each client has Medicaid for the month. Therefore, there are also columns with names like April, May, June where we mark yes or no for each client. Some of the worksheets also have information about the clients' guardian, phone number, etc., but not every worksheet has those. (I can't show you the worksheets b/c of federal privacy laws, of course.)

I really, really want a worksheet that's a master list of the data from each of the existing worksheets, and I'd like to keep it synced to the source worksheets. I'd love it if I could also add a column telling me which of the source worksheets the data originally came from.

I've tried a few things to make it happen, but nothing has worked.

I've tried using the Consolidate function built into Excel as well, but that only works with numbers, not text, and it wants to manipulate the numbers instead of just copying them over.

I've found several macros online that should have worked (including one from this site) but that I wasn't able to install to my worksheet. I followed all the steps for installation, and throught it worked, but when I went back to the Excel file to run the macro, the list of available macros remained emptpy.

Is it possible that corporate IT has disabled the ability to install macros? Is there somewhere in Options were I need to go to turn Macros on?

View 5 Replies View Related

Create Multiple Workbooks With Names Based On List

Sep 2, 2007

I have some very tedious work to do in Excel:

table looks like following:
DepID name function
S1 a YY
S1 b XX
S1 c ww
S2 d oo
S3 e ii
S3 f ll
S4 t mm
. . . . . .. . . .
. . . . . .. . . .
. . . . . .. . . .

S7999 u ee
S7999 w aa

My task is to create new folders for each department according to DepID, which means if there are 7999 departments, I have to create 7999 folders, any VBA code can do this?

View 9 Replies View Related

Automatically Create List Of Column Contents Based On Criteria?

Jan 15, 2014

I have a spreadsheet that lists dates in row 6, columns V through BE. We use these dates to log attendance for kids, so under each date there may be one of many symbols, such as "x" if the student was there. I would like to create a list of the dates that meet one of two criteria: either blank or containing "WR". So, let's say column Z is blank and Y has "WR" in it. In column FB I would like a list of all of the dates that were blank or WR, so I would want the dates contained in row 6 for Y and Z. Is there a function that will 1) look for a blank or WR across a row, 2) find the associated date for that blank in row 6, and 3) write the in another column in the form of a list, each date separated by commas? Here is an example of my information. I would like for a function to automatically make the list in column FB for any dates that are blank or contain "WR".



List of Dates Absent



1/23, 1/24


View 2 Replies View Related

Create A List Of Unique Names From A List Of Multiple Names

Oct 21, 2009

I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.

What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.

The code I have so far is below:

Public Sub find_managers()
Dim managers1 As Range
Dim names1 As Range
Dim n1 As Variant
Dim n2 As Variant

In my mind it should check the names in the unique list against the imported list and add any missing names.

View 9 Replies View Related

Create Dropdown That Will List All Names Starting With Initial Letter Of Names?

Jan 13, 2014

I have a long customer listing, names 5 to 36 characters and several with their location in the name as well as a few common duplicate names. I am trying to produce sheet where the customers name once selected opens in the customers spreadsheet and data can be added/amended for sales etc.The lists are not in alphabetical order as when created a customer number is automatically allocated. My aim is just to type in the first letter of the name and the dropdown appears the customer is selected and their card appears. I have tried data validation, lookup, vlookup, Dropdown and Match/find. they only return the first record found and no sign of any others. Find returned all instances of the letter appearing in every name.

View 9 Replies View Related

Can Create Data Validation List Of Names Created In Name Box Or Sheet Tab Names

May 7, 2012

Can I create data validation list of the names created in the name box or of the sheet tab names?

View 5 Replies View Related

Relating List Of Random Numbers To A List Of Names To Create Rota

Nov 21, 2013

I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).

View 3 Replies View Related

Creating New Worksheets From A List Of Names

Oct 11, 2006

debugging issues without changing the code dramatically. I think I've basically "got it" but there are a few lines that I think are problematic. I've written notes to highlight my thinking.

Let me give a basic discription of what I'm trying to do: This file lists where a company has offices. I'm want my subroutine to 1) Sort the cities in alphabetical order (this occurs at the end of the code), 2) for each of the cities listed in "AllCities" worksheet, check whether there is a additional corresponding worksheet of the same city name, and if there isn't one, the subroutine would automatically add it, and 3) delete any worksheet of a city name that is NOT found in the listings found in the "AllCities" worksheet.

View 8 Replies View Related

Create List From Multiple Names

Nov 7, 2008

Have a list of aprox 50,000 names. in that list names may repeat themselves multiple times. Is there a way in excel to extrapolate from that list of 50k to display only a single instance of each name in a separate Colum?

View 2 Replies View Related

How To Create Sheets From A List Of Names

Mar 1, 2012

I have a macro that creates sheets from a list of names. I have a template which is copied.

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range
Dim MySheetName As String


Sometimes I have to update that name list.

What I need is to have some addition that checks if the sheet does exist, if it does I want to skip and move on to the next name.

View 3 Replies View Related

Create List Of All Named Range Names

Oct 17, 2006

I have a worksheet where a number of the cells have been assigned names. I want to copy these cell names & create a list on another part of the worksheet. Does anyone have some code I could use to achieve this?

View 8 Replies View Related

List Names In Alphabetical Order From Multiple Worksheets

Mar 3, 2007

I'm trying to get this formula to work across multiple worksheets. It suppose to list names in alphabetical order. I'm using these define names to get it to work. Also may I have another formula that list unique names in alphabetical order?


=IF(ROWS($E$2:E2)<= COUNTIF(Col_A,"?*"),INDEX(Col_A,MATCH(LARGE(COUNTIF(Col_A,">"&Col_A),ROWS($E$2:E2))=S+1,COUNTIF(Col_A,">"&Col_A),0)),"")

View 9 Replies View Related

Loop Through Multiple Files To Create List Of Tab Names

Jul 26, 2013

I have a folder with 20 Excel files. I'm trying to create a master list of all the tab names. I can see all of the files opening, but it only copies some of the names.


Sub GetTabNames()
Dim wkBook1, wkBook2 As Workbook
Dim stFilePath1 As String
Dim FileList(1 To 18) As String
Dim iLoopSheet, iLoopProg As Integer
Application.ScreenUpdating = False

[Code] .....

View 2 Replies View Related

Create Sheets With Month Names By Date List

Jan 11, 2008

I'm trying to achieve is to write a macro that can search a column of dates then open new worksheets according to the months that are present in the column of dates. So, for example, if the column has dates ranging from January to June, I need the macro to open 6 new worksheets and label them January, February, March, April, May and June.

View 6 Replies View Related

Macro - Automatically List All Worksheets Within Workbook?

Feb 7, 2014

How do you automatically list all the worksheets within a workbook? I did some searching and decided to use the following code:

Sub ListWorksheets()

'Lists all the sheet names in the workbook onto the _
first sheet (starting at cell a4)

Dim i As Integer
For i = 1 To Worksheets.Count
Worksheets(1).Cells(i, 1) = Worksheets(i).Name
Next i

End Sub

It would be very useful if I could control the row in which the data is returned. "starting at cell [U]a4[U] seems to be that part of the command, but it still returns in the 1st row.

View 3 Replies View Related

Create Range Names Based On Corresponding Cells

Aug 5, 2008

I want to make several charts with dynamic ranges. To do that, I wrote the offset functions I need in cells. I've attached a sample spreadsheet. I want to have a macro so that when I run it, it will take the contents of the active cell, insert a name called those contents, and make it refer to the cell 4 to the left of it.

In the spreadsheet, I'd like to be able to click on E2 (sentdate1), create a name called sentdate1, and make its value A2, or =OFFSET('Sentiment'!$A$3,0,0,COUNT('Sentiment'!$A:$A),1). Here's what I have so far.

Sub Macro8()
ActiveCell.Offset(0, -4).Range("A1").Select
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveWorkbook.Names.Add Name:="sentdate1", RefersToR1C1:= _
End Sub

Auto Merged Post Until 24 Hrs Passes;Here's the sample worksheet.

View 6 Replies View Related

Automatically Create List / Table From Data That Changes Often

May 30, 2013

I have a client list/table that gets new data each day. (ALL) - see file

I need to create separate lists/tables on new sheets from the main data sheet (ALL) base on certain conditions/criteria...

On the (ID) sheet I need all the clients that have ID numbers to display, on the (Reg no) sheet I need all the clients with Reg numbers to display... etc.

Like I said that the main data sheet grows each day and the lists/tables on the other sheets needs to automatically include all the new clients that's entered.

Is there a way to do this using formulas or do I need code to do this?

View 2 Replies View Related

Create Command Buttons Based On Worksheet Names

Jan 18, 2010

I am at it again and maybe I am asking to do something that is not possible. I am trying to build a program that will allow the user some flexibilty down the road if I am no longer here to support my code. I have a workbook that contains several sheets. The sheets are labled by equipment name. The Sheets themselves contain specific data with regards to the equipment. What I want to do is as follows. Populate a user form with command buttons based off of the worksheet names. The reason for this would be if 2 years from now we add a new piece of equipment all the user will have to do is add a sheet in the workbook and the user form would reflect the new sheet with a new button. Like wise if I delete a sheet in the workbook the user form would refelect that change as well.

View 14 Replies View Related

Copy Column From All Worksheets & Create 1 List

Oct 30, 2006

I have the below code, I want to end up with values in column 'A', and a hard coded value in column 'B' for each row in column 'A'. But, I'm ending up with the hard coded value just in the first row of column 'B'. In other words, I want to repeat a value and copy it down column 'B' the same number of times that there are values in column 'A'. Here is what I am working with so far that is producing just the value in the first row of column 'B' -

Private Sub dispnames()
Dim b As Integer
Dim r As Integer
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets

View 7 Replies View Related

Create List From Same Cell On Multiple Worksheets

Oct 11, 2007

I'm simply trying to take for example cell A1 from multiple sheets and list them vertically on a master sheet. When you drag it down it does not alter the sheet number, that just stays static.

View 3 Replies View Related

Macro To Create Worksheet 'xxxx' And List All Worksheets In Workbook

Feb 2, 2009

I am after some code to create a new worksheet called 'xxxx' in my workbook, and then list all the sheets within the workbook (excluding the newly created 'xxxx' sheet.)

View 4 Replies View Related

Create List Of Duplicates Based On Occurrences In Another List

May 27, 2014

Eng list.xlsx

The attached file (a copy of my main one) has a list of our engineers, and what stock they carry. The stock parts are the 64, 65, 66... numbers.

I need to create a list from this (as underneath the main table), for all instances where there is a 'Y' in the columns next to each engineer. So if an engineer has 3x pieces of stock, they need to appear in the list 3x times. If they have 1x piece of stock, they appear in the list once.

View 8 Replies View Related

Create New List Based On Data From Another Dynamic List

Jan 4, 2013

I've attached a spreadsheet showing the effect I'm trying to achieve. A list (which changes weekly) contains employee details and length of service. Based on the length of service I need to build a new list grouping the employees based on the service length.


View 6 Replies View Related

Automatically Create Named Range Based On CheckBox

Jan 20, 2008

I am quite new on this forum.

I would like a dynamic range to be multiplied with 2. The Range is always start in cell H14 to P?

I have multi checkbox in column F,

So here is an eksampel: If checkbox in F4 is true then Range H4:P4, should by multiplied with 2 else if the chekbox is FALSE, then only multiply with 1.

I want to entry data in the Range H14:P? by this code

Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

View 9 Replies View Related

Automatically Create Columns Based On Difference Between Two Dates

May 2, 2008

I have been perusing various Excel sites in hopes of finding a macro or function that can create columns based upon two dates input into specific cells. My ultimate goal is to use this macro or automated function so that when the user inputs the dates, the columns would automatically be created for them, rather than having them do it manually.

ideally, the 'start' and 'finish' dates are named cells and the macro could determine the difference between the two and automatically insert columns to the left of a ' Totals' column.

View 9 Replies View Related

Automatically Create Worksheet ComboBox Based On Selection

May 16, 2008

I am working on a spreadsheet for a client. I am using Excel 2007 and she is using Excel 2000. I have created a emplate for her to enter survey data into. Using the following code, I have created combo boxes for the drop-down menus so the client can tab from one cell to the next while entering data:

Option Explicit
Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
'Hide combo box and move to next cell on Enter and Tab
Select Case KeyCode
Case 9
ActiveCell.Offset(0, 1).Activate
Case 13
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub.....................

View 6 Replies View Related

Create New Worksheets Based On Range On Other Worksheet

Dec 21, 2011

I have file contains two worksheets. 1st worksheet named "list" and the 2nd one named "Template". I have a range in WS "List" let say a1:d20 that contains names. This range is not fix, it might be more or less. I need creating new worksheet using worksheet "Template" for the new sheets in the mentioned range as follow:

"Create new worksheet for each name in this range."

List is not unique, some names are duplicated. If the name repeated, create only one. Use worksheet template for each new worksheet created.

View 2 Replies View Related

Copyrights 2005-15, All rights reserved