Creating Multiple Workbooks Based On Template
Feb 18, 2010
I am trying to create a workbook in which each person gets a seperate sheet.There are common cells in each sheet like name addrss,phone number, etc (about 10 such cells) and there are other cells which will be unique to each person like hyperlinks to letters I have sent to that particular person.
The tab will be named using that person's surname. (I use "find" on the surname and i can locate the particular worksheet).I can add the unique ones as and when needed in each particular sheet, but what i would like to do is to create the first sheet containing all the necessary common cells with a blank cell next to it and then (maybe run a macro?) to create multiple sheets all containing the same info so that with each sheet I only have to fill in the blanks (name,address,age,etc) .
At the moment I am using "copy sheet" each time using an unfilled template sheet and it is a pain.
View 9 Replies
ADVERTISEMENT
Apr 4, 2014
I need to create multiple workbooks from a template, but the header on the sheets need to change based on a list of numbers. I know you can generate multiple sheets from a template within the same workbook. Can you export all the sheets once they are generated to separate workbooks/files? Also I need to change the text in lets say cell A1 on every sheet based on a list from the main sheet. So the main sheet has a list in column A: 1, 2, 3, 4 etc. Once the sheets are generated from the template cell A1 on each generated sheet needs to have a different number from the list i.e. 1, 2, 3, 4 etc. It would be nice if i can set the format of cell A1 on the generated sheets so when then number is inserted it large text and bold.
View 4 Replies
View Related
Aug 20, 2013
The easiest way I can describe the scenario I am trying to create, is to use a company list of personnel (my index) and to generate a time-sheet workbook per person based on a pre-populated template. This time-sheet is saved as the persons name and has the persons name entered into cell D:10
I have a workbook which contains two worksheets;
1) An Index sheet which contains a list of names that I wish to use in Q16 downwards (note the length of this list will vary each time I run this)
2) A "template" sheet which I wish to duplicate in new workbooks
3) A second "data" sheet that I wish to copy across in new workbooks
I need a macro that will take the "template" and "data" sheets and copy it into a new workbook, renaming each new workbook to each name in my Index sheet. I also want that same Name to be copied into cell reference D:10 of the "template" each time.
The end result is that I should have a series of new files generated and saved which are named the same as the Index list, with both the "Template" sheet and the "Data" sheet present, with the cell D:10 pre-populated with the Name provided in the "Template" sheet.
I'm using Excel 2010.
View 9 Replies
View Related
May 19, 2014
(Code at the bottom of this message)
I have a file with a roughly 20 workbooks. I need to split the data in each workbook into two sheets, rename each sheet, then export the data to template with multiple sheets. After splitting the data into the appropriate sheets in the original workbook, Sheet1 in has only 1 row (plus header) and needs to be transferred to "Project Entry" in the template; Sheet2 can have any number of rows (less than 100) and needs to be transferred to "Activity Entry" in the template. I need to save the template based on the value of cell A1 on "Project Entry" sheet of the template. The organization of the columns in the original workbooks is different than that of the template, so this is not a straight copy and paste operation. The cell formatting in the template must be retained.
I have half of this under wraps. I've cobbled together some code that splits the original data where I need it split, copies it to new sheets and saves the files. I now need to figure out out to get it out of this saved file and into the template.
The crosswalk of values between the original data (Sheet1) and the template(Project Entry) needs to look like the below. So, variable 1 exists in column B of the original and needs to populate column B of the template, and column Q in the original needs to populate column G in the template. The variables and column relationships are different for Sheet2/Activity Entry.
SPREADSHEET COLUMN
Variable
Original
Template
[Code]....
View 3 Replies
View Related
Oct 5, 2011
I have a workbook that has a sheet called CustInvData, this sheet contains 4,421 rows of invoice transaction data for 178 customers starting on row 2 (headers on row 1). I need to split the transaction data for each customer out into a workbook template based on the customer name in column A. I need each workbook named by the customer name along with a month and year (example: Bellsouth-0911.xls), this should create 178 unique workbooks. And since we sometimes have to go back and rerun invoices for previous months, I'll need to control the month and year manually in the code.
The parsed data needs to be copied to a pre-formatted invoice template. This template has 2 sheets, Sheet1 is called 'Product Summary', this is a table that uses VLOOKUP functions to read the data in Sheet2 called 'Product Details', this is the sheet the parsed data needs to be copied to for each customer invoice. The 'Product Details' sheet has formatted rows 1 thru 11, row 11 being the header row for the data from CustInvData to be copied. So the parsed data needs to start at row 12.
Last, once the data has been copied into the 'Product Details' sheet, I need the data to be SubTotaled at each change in column J (Product) and use the 'Sum' function to add a Subtotal in column L (Retail Price) for each unique product category.
Example data below, I've simplified it (the actual data array spans from columns A to Y)
Customer NameProduct Retail Price
ABC CompanyAVMPCR10
ABC CompanyAVMPCA15
[Code]....
I'm a bit of a novice with macros, but I know Excel pretty well.
Using Excel 2007 running on Windows Vista
View 2 Replies
View Related
Jan 23, 2007
I have just started out learning vb and am having some trouble with a particular request. The following code is code I found to split a worksheet into different sheets based on a variable in the data (Col G). It has now been requested that instead of new worksheets, they want the data in new work books, automatically named and saved as today's date.
Sub UpDate_Sheets()
Dim ws As Worksheet, source As Range, dest As Range
Dim daily As Worksheet
Set daily = Worksheets("Sheet1")
Application. ScreenUpdating = False
On Error Resume Next
For Each ws In Worksheets
If ws.Name <> daily.Name Then
daily.Range("G1"). AutoFilter Field:=7, Criteria1:=ws.Name
Set source = daily.Range("G1"). CurrentRegion.Offset(1, 0).SpecialCells(xlVisible)
Set dest = Worksheets(ws.Name).Range("A65536").End(xlUp).Offset(1, 0)
source.Copy dest
End If
Next
daily.Range("G1").AutoFilter
On Error Goto 0
End Sub
View 4 Replies
View Related
Dec 14, 2007
For work I have to create copies of the same workbook for every day of the month. I have got the following to do it properly, which may not be the best way so I am open to suggestions, but I don't want it to save this macro in every workbook. There are however other macros that I do want it to take with it, just not this one.
Sub SaveByDate()
Dim a As Integer, b As Integer, c As Integer
' number of days in month + 1 (this is just a test; for Jan a = 32)
a = 6
' start date
b = 1
c = 0
For c = 1 To a
If c < a Then
MyMonth = "Jan"
MyDay = b
MyFileName = MyMonth & " " & MyDay & " 2008 daily report"
ActiveWorkbook.SaveAs Filename:=MyFileName
b = c + 1
End If
Next c
End Sub
View 9 Replies
View Related
Aug 30, 2013
I have two worksheets in my report cards:
1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.
Student
Math
Reading
Science
Jimmy
75
84
100
[code].....
2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.
Student Name
=Data!$A2
Math
=Data!$B2
Reading
=Data!$C2
Science
=Data!$D2
How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?
I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.
View 1 Replies
View Related
Nov 12, 2007
I need a little help creating the macro for a template file which is going to be used for data input and further analysis. I have a set of arrays, which can be altered. The arrays may look like so:
View 9 Replies
View Related
Feb 2, 2010
Private Sub AddWorksheets()
For Each c In Sheets("Worksheet Names").Range("A1:A24")
If c.Value "" Then
Worksheets("Sheet1").Copy
Sheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = c.Value
End If
Next c
End Sub
Strange....
I have a list of worksheet names in Sheets("Worksheet Names").Range("A1:A24").
I need to create a new worksheet for each listing and use sheet1 as the template for all...
The above code creates 22 blank WORKBOOKS, not worksheets.
View 9 Replies
View Related
Jul 24, 2013
I need taking thousands of line items of raw data and creating a model that can present the totals in an easy to read format. I've attached a sample of what my data dump looks like....though the actual dump is thousands of line items.
Data.xlsx
View 1 Replies
View Related
May 14, 2014
I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.
Sample:
Location A Class:AA Segment: Sports Bar Rep: John Smith
Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1
I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.
I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.
View 2 Replies
View Related
May 22, 2006
I am creating an excel worksheet. Every time a user opens the worksheet (from a template), I want to get the next numeric sequence number as a reference, showing it in the sheet. The template is available to multiple users but the sequence number should be unique, ie if user Tom has sequence 1, Jerry will get 2, then Anne will get 3 and Tom's next one will be 4. I considered an ASCII text file with a number in, eg 1. Upon opening get OPEN text file, READ text file, Convert to numeric.. add 1.. use this. Convert to string.. WRITE text file. It seems clumsy and since I can only open with READ or WRITE (append no good, since I want only 1 value held), the READ allows multiple users to access simulataneously, and potentially get the same sequence.
Is it possinle to:
LOCK file
OPEN file for read
READ file
CLOSE file
OPEN file for write
WRITE file
CLOSE file
UNCLOCK file
There are likely to be several users accessing the template and the chance of them opening it at the same time is limited, but possible.
View 3 Replies
View Related
Jul 31, 2013
I have a worksheet with the following columns:
Brand
UserName
FirstName
LastName
Email
[Code]....
This is a relatively long list - 1000's. What I'd like to do is create a macro that sorts by brand, and at every change in Brand, copy the user details (Username, firstname, lastname, email) to another workbook with those labels at the top of the table. Upon completion, it saves the workbook with the name of the appropriate brand.
E.g. I'd have 3 workbooks:
- BrandA.xlsx - with the 2 user listed
- BrandB.xlsx - with the 1 user listed
- BrandC.xlsx - with the 1 user listed
View 3 Replies
View Related
Apr 8, 2007
I have multiple workbooks in a particular folder. Each workbook has only one worksheet named "Data". I need a VBA script that could open each file in the folder, copy only particular pre-defined cells that have been mentioned in the script, and then place it one below the other on a new sheet.
Ex, if the pre-defined cell is A2, C6, and D7. I want the code to open all the sheets in the folder one by one, copy A2, C6, and D7 of each workbook opened, and paste it one below the other on the new workbook that is running the macro.
As an add-on, it would also be great if the next column cud carry the file name of from which workbook the information was picked out.
The result thus in the new workbook wud be like...
A2's data | C6's data | D7's data | File Name
Andrew Male Married march3.xls
Peter Male Un-Married march4.xls
Susan Female Divorced april2.xls
View 9 Replies
View Related
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
Apr 16, 2014
I have created timesheets for employees that work in our shop. Our company manufactures products for different industries, such as mining, wind power generation, general industrial applications, and so forth. I modified some time sheet templates I found for excel to accommodate our company's actions. Each employee has their own workbook, in which the months are separated into different worksheets. Each sheet is divided further into weeks and in each weekly section the areas of information are divided as follows:
A / B / C / D / E / F / G / H / I / J / K / L / M
Work Sector / Workshop or Fieldservice / Scope of Work / Job # / Reg or OT / Mon / Tue / Wed / Thu / Fri / Sat / Sun / Total
There are 7 workbooks (one for each employee), each with 12 sheets (one for each month). I want to create a master sheet that will pull information from everyone's timesheet if they worked on a particular job. In other words, I would like to type a job number into a cell, then have excel look through everyone's timesheets and pull over only the rows of information that contain that job number
View 12 Replies
View Related
Jul 6, 2008
I have an excel file which contains following data in it.
Col-A Col-B Col-C ......
Cust Cd Name Sales
=======================
101 AAA 1000
101 AAA 500
101 AAA 3000
102 BBB 800
102 BBB 200
103 CCC 200
103 CCC 200
103 CCC 200
I need to create following three workbooks with name based on Cust Cd from above excel file.
Workbook - 1 : 101.xls which contains records only pertaining to Cust Cd 101.
Workbook - 2 : 102.xls which contains records only pertaining to Cust Cd 102.
Workbook - 3 : 103.xls which contains records only pertaining to Cust Cd 103.
View 4 Replies
View Related
Apr 22, 2014
I tried all failed.We have about 160 different workbooks (one for each business unit) stored online. Staff enters information about their weekly revenue and expenses and here at head office I collect that information and consolidate them.What I am trying to do is that;1.) Create a master Workbook with ~160 worksheets (One worksheet for each unit) named exactly the same with other workbooks2.) And macro can pull the information from related files stored in a certain folderIt is very much like another members problem but I am not sure why I cant get that code working for me? [URL]
View 5 Replies
View Related
Nov 1, 2009
Using the first worksheet "sheet1" I need to create individual worksheets each with a name for the different rows in column A, they also need to be arranged in that order. I found what appears to be the the first half of my solution on this thread for creating + naming the worksheets. Thank you Donkeyote :-)
http://www.excelforum.com/excel-2007...-of-cells.html
That macro does almost everything but if someone was to delete a worksheet, or move it around it would not be in sync with the starting list. I need to arrange the worksheets based on the list on "sheet1". The list is going to be ever growing and will probably be re-arranged multiple times. I'm sure it's something really easy but I just can't figure it out.
View 3 Replies
View Related
Jul 19, 2012
I've been attempting to find a macro that would combine a number of similar workbooks into one large workbook. The files I'm dealing with are all .csv files with identical columns and headers.
I found one macro here: see below (this is the start of the macro) yet either I'm not using it correctly or there are errors in it as when I run it I get an error that says Run-time error '9' Subscript out of range and gives me the option to debug it. When I choose to debug it opens up the macro and has the following section highlighted?
Set wsMaster = ThisWorkbook.Sheets("Master") 'sheet report is built into
The macro in I found on this site begins with the following:
Option Explicit
Sub Consolidate()
'Author: Jerry Beaucaire'
'Date: 9/15/2009 (2007 compatible) (updated 4/29/2011)
'Summary: Merge files in a specific folder into one master sheet (stacked)
' Moves imported files into another folder
View 3 Replies
View Related
Apr 20, 2012
I have written a 2007 workbook which contains 4 simple macros. One of the macros automates the process of saving the print range as a .pdf file. It works fine on my pc but when I send it to others to use, when they try the macros, they all return a 1004 runtime error.
View 6 Replies
View Related
Feb 21, 2013
how to create a copy of my employee attendance template. Is there a way to create a copy of the template by entering an employee name in the "name" cell of the template and it automatically renames the sheet that employee name and also saves or recopies the template for use with the next employee?
View 8 Replies
View Related
Jan 4, 2009
I have created a tab that will be used as a template. Is there a way to quickly duplicate that tab in the same workbook 500 times?
View 9 Replies
View Related
Feb 14, 2008
i have created a diary using multiple tabs. I would now like to create a template that i can paste into the multiple tabs. I have tried to copy and paste but the format ends up all wrong, I would also like to paste all the tabs in one go
Regards Johnt Auto Merged Post;I dont have to paste anything all i have to do is select all sheets on the first page and then design my template on that page which will show on all pages
View 2 Replies
View Related
Oct 22, 2007
I have some experience with excel, but until now have not ventured into VBA and macros.
I have a workbook which will have the following sheets:
1.Absence Summary sheet - Summarises data from each employee's individual sheet.
2. Template Sheet - A sheet formatted as an absence record sheet, but without data.
3. Individual employee Absence record sheets - Based on the Template sheet.
I have read with interest the various posts and help files on User Forms & Macros, but have got a bit stuck.
My Aim: ....
View 11 Replies
View Related
Feb 9, 2014
I have a master template with columns running from A to Q. Most of what she wanted is fairly simple 'if' statements, however she also wants to move the contents of one cell to an adjacent sell based on the number exceeding a certain value,
[Code].....
My problems start when I want to create a new sheet/tab and let her name it. I would have simply created 12 months for her, unfortunately there is a sheet for a number of people and the start of the year can vary. My simple solution was to create a macro that copies the original Template (effectively sheet 1) and puts it into the newly created tab. However this does not copy the above VBA code across
[Code] .....
View 1 Replies
View Related
Nov 10, 2011
I'm trying to create a workbook with multiple worksheets. My first sheet to be named "Main" and each additional sheet to be named in sequence after the values in the cells between B5:B98 on !Main.
Example:
B5 = "01"
B6 = "01.1"
B7 = "01.2"
B8 = "02"
B9 = "03"
etc...
I'd like to have a macro that would take a worksheet in this workbook named "Template" and make a copy for each cell between B5:B98 and name that new worksheet "c" and then the value in each of those (B5:B98) cells.
Example:
First worksheet named "Main"
Second worksheet named "c01"
Third worksheet named "c01.1"
and so on.....
View 1 Replies
View Related
Aug 18, 2006
If you have a workbook or a template open you can find out the full path of the file from the .FullPath property. My problem is that when a user double-clicks my .xlt file (to create a new workbook based on it), I want the code in the Workbook_Open event to be able to tell the full path of the template it was created from (because the code will later go on to save the new workbook under a date related filename in the same directory that the template was located in). I haven't been able to find a property that gives me any clue as to the location of the template this new workbook is based on.
View 5 Replies
View Related
Jan 6, 2009
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
View 9 Replies
View Related
