Macro To Create Multiple Workbooks Based On Data
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
ADVERTISEMENT
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
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
Jun 27, 2014
I have data in Column C that looks like this:
John
John
John
Ted
Ted
Ted
Gary
Gary
Is there a macro that can create workbooks based on this data. So, i'd have one workbook that would contain all the Johns and all the other data in Columns A -Z, then another workbook for Ted, and so on...
The number of workbooks I need is not fixed. It all depends on how many names are in column C.
View 14 Replies
View Related
Jun 27, 2013
VBA Macro to work through a worksheet that consist of static data (tab 4) cost centres and to populate a new work book per cost centre consisting of three tabs for every cost centre found in the static data.
The master Workbook has the following tabs:
Tab 1 is called travel and consist of column a which is the cost centre number (plus 14 other columns)
Tab 2 is called Mobile and consist of column a which is the cost centre number (plus 14 other columns)
Tab 3 is called Expenses. and consist of column a which is the cost centre number (plus 14 other columns)
Tab 4 Static Date column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns).
If no information found on a specific cost centre, the tab will include the headers and return the words "No transactions for this period"
Whilst splitting data into Tabs the workbooks should check against the Static Data table and include cost center description in Column B of each tab in the new workbook.
If master workbook consist of the following....
Tab 1 is called travel and consist of column 1 which is the cost centre number (plus 14 other columns), cost centres, 555,557,
Tab 2 is called Mobile column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns) cost centres, 555, 78689,
Tab 3 is called Expenses. column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns), cost centres, 555,
tab 4 Static Date - column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns). cost centres, 555, 557,78689
It should output 3 workbooks by cost centre number.
One for 555, which consists of 3 tabs, travel, mobile and expenses.
A second for 557 which has 3 tabs travel, mobile and expenses, but only with data in the travel tab.
A third for 78689 which has 3 tabs travel, mobile and expenses, but only with data for mobile data.
The workbooks will be replicates of the contents within the tabs where column a wil be the cost centre plus 14 additional columns.
View 5 Replies
View Related
Mar 18, 2014
I have a TEMPLATE workbook that has 106 cells (all in the same worksheet) that need to have data input in them.
I have a separate DATA workbook with 3,000 rows of data, each row has 106 columns that correspond to the cells in the TEMPLATE workbook.
I need to create 3,000 new workbooks that are populated with the data from the DATA workbook.
View 3 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
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
Aug 4, 2012
I have 17 workbooks which contain similar data and formatting. The only variance is the number of rows of data in each. I want to write a macro in the Master worksheet that will accomplish the following
1. Create a worksheet name in the master workbook with the same name as the workbook that it's pulling data from.
2. Copy over the column headings from columns A:P to the newly created worksheet on the master workbook
3. Copy over all rows that contain the word "down" under column D titled Operational status
The naming convention for the master workbook will be titled "All Switches". The naming convention for the worksheets containing the data are titled Port_Map_on_tiegcsw01, Port_Map_on_tiegcsw02, Port_Map_on_nypgcsw01. There are other names , but I can reference the code an update accordingly.
I found some searches on the forum for creating a macro to copy data between workbooks, but not quite sure how to write the macro for the three functions.
View 2 Replies
View Related
Sep 15, 2008
I have been given the following code to transfer data from multiple workbooks within folders and subfolders to retreive the same line of data from each of the workbooks and place them in a master workbook.
the folders are set up as follows,
there is a main folder, (a yearly folder)
within this are 12 monthly folders (named January to December)
within these are four weekly folders (named week 1 -week 4)
contained within these weekly folders are the workbooks that i wish to copy data from.
for example a1 - k1
the code i am using transfers the file names but comes up with #REF! instead of transfering the data
Here is the
View 9 Replies
View Related
Apr 2, 2014
I jst need to console data from different workbooks to one worksheet. There are 30 workbooks & each workbook contains one worksheet name as Data. The work i am currently doing mentioned below step wise
1) Open workbook
2) Apply filter on whole data
3) Select dates from "J" Column (E.g 02/10/2014 to 02/20/2014)
4) Then Copy whole filtered data
5)Paste into New workbook
6) Open another worbook
7) repeate Step # 2,3
8)Then paste whole data in below the first data New Workbook (In which we have paste the first data below that i will paste this data)
These steps i have to do manually it takes almost 1hr daily After pasting all 30 workbooks data in one workbook i have to make pivot of it & sent it to my Manager. What i need
I jst want a excel macro that will ask me date range from which i have to pull data from all 30 workbooks.
Once i gave date range will jst click on button say extract or pull. It willl console whole 30 workbooks data from the given date range in a one worksheet.
View 13 Replies
View Related
Jul 3, 2013
I need to consolidate a lot of information from multiple workbooks all the workbooks are located in a folder, i am not bothered about running each one separately or a group at a time, each work book has ten sheets with each sheet in the workbook being different, it needs to add to the next blank row on each sheet.
View 1 Replies
View Related
Jun 3, 2009
On a weekly basis, I receive a single worksheet in a workbook that contains ~30,000 rows of product sales.
Row one contains column headings that is unique to all other rows. Column A contains the store number that sold the product. There are ~50 unique store numbers.
I am trying to create a macro that will break the report up into seperate workbooks.
For example, assume in column A there are 30 rows of data for 3 different store numbers (say store 112, 386, & 798, each with 10 rows of data). I want to create 3 new workbooks and include the same column heading for each. For example, name new workbooks as follows: "Store 112", "Store 386", "Store 798".
View 7 Replies
View Related
Feb 22, 2012
Every two weeks: During Payroll, we have a list of managers and specific employees under that manager.
The managers name is in Column A and they repeat.
Normally we filter that manager, copy and paste the data into a new Workbook, save it, and send the info out to that manager. We do not want other managers to see the information provided to the specific manager.
This is a long tedious process as there are close to 30 managers.
1. Is there a way to run a macro (presumably based on a validation list) to filter, copy the worksheet into a new workbook for each of the 30+ managers?
2. Is there a way to save each with a specific name like "Payroll Feb11 - Manager Name.xlsx"
View 9 Replies
View Related
Jul 8, 2014
I'm creating a macro that will involve some form of if-else/case-switch, as well as a loop (probably), but I'm not too sure how to go about it.
Basically there are records in columns. Lets say Column A has numbers in it, for example
Cell A1 has "Three"
A2 has "Five"
A3 has "Two" all without quotes
I basically want a macro that will loop through the whole column, so if A1 contains "Three" (not case sensitive), then set B1 to "3" without quotes. If A2 contains "Two", then the macro will set B2 to "2" without quotes.
Of course this is sample data but you get the jist. It needs to loop through one column, so as to fill in the other column. You can assume that column A will always be filled, so the macro is being created to automatically fill in column B based on what is in column A.
View 1 Replies
View Related
Jul 6, 2014
I'm trying to compile a spread sheet that will pull the play by play logs from the NHL website.
The logs are listed in order.
Game 1 is [URL]... Game 2 is [URL]... etc etc
I am trying to put them all on one spreadsheet. (or to do it in batches of 100, or 50 or whatever the size of the file allows)
View 1 Replies
View Related
Apr 28, 2014
I expect to deploy over 200 iterations of a workbook (templates if you will) containing a mix of up to one hundred different attributes each with its own list of valid values. My problem is the attributes don't always default to the same column in every template workbook. Therefore, to make things easier for users I would like to create attribute valid value dropdown lists on the fly using the "Valid Values' table as the source data (see the attached). Since the attribute names will be the same from one template workbook to the next, I wanted to come up with a macro that would reference a source table (a separate workbook) containing all valid values for all attributes which users would be required to download to their local machine.
See the attached example workbooks and an example of a "Valid Values" list. Here's what needs to happen: When a user opens template workbook #1 I need drop down lists containing the valid values for the attributes contained in cell B3 (User Code),C3 (Department) and D3 (Category). Likewise, when another user opens template workbook #2, I need drop down lists containing the valid values in cell B2 (Department), C3 (Group), D3 (Category), E3 (Level), F3 (Material) and G3 (User Code). Notice that "Department", "Category" and "User Code" are data elements common between both workbooks but resident in different columns.
View 2 Replies
View Related
Oct 9, 2012
I have a workbook with about 10000 rows of data for about 100 suppliers in Sheet1 and about 15000 rows of same Suppliers payment details in sheet2.
What I am doing now is:-Filter every supplier names in Column A of sheet1, copy all the rows and paste in sheet1 of a new workook & Again copy the Payment details of a same supplier from master file sheet2 and paste the same in sheet2 of this new workbook then save the files with the supplier name as file name in my documents folder code, that filters each suppliers data from sheet1 & 2 and paste in new workbook sheet1 & 2.
View 1 Replies
View Related
Jul 23, 2008
I need to create a macro to move variable multiple horizontal data to vertical format with certain infomation on horizontal will be duplicated following that variables. It's looks like below where you can see variables data in column F, G, H and I are moved vertically and at the same time column A, B, C, D and E will be duplicated following the variables allocation. I've tried to use transpose but it too manual and now looking suitable macro to help on this function
Original DataAccountDim 3Dim 4AmountCurrencyV20228V20242V20211V202044006003300BXXX 9.4USD0.591.923.343.554006003400BXXX 88.17USD5.5118.0331.3233.314006003500BXXX 7.27USD0.451.492.582.75Process to automateAccountDim 2Dim 3Dim 4AmountCurrency400600V202283300BXXX 0.59USD400600V202283300BXXX 1.92USD400600V202283300BXXX 3.34USD400600V202423300BXXX 3.55USD400600V202423400BXXX 5.51USD400600V202423400BXXX 18.03USD400600V202113400BXXX 31.32USD400600V202113400BXXX 33.31USD400600V202113500BXXX 0.45USD400600V202043500BXXX 1.49USD400600V202043500BXXX 2.58USD400600V202043500BXXX 2.75USD
View 9 Replies
View Related
Feb 24, 2014
I want to find a way to create multiple worksheets and matching data to be placed on appropriate sheets.
Here are more details (Please check the attached sheet screenshot as well):
Excel_Macro_Requirement.jpg
In a workbook, there is a "Master-Sheet". This master sheet contains 8 columns.
I want to create as many new worksheets after master sheet as the values are there in column B (Column 2 i.e. Ad Group). In above attached screenshot, there are 8 values (B2:B9 or A-H). So, I would like to create 8 new worksheets after the master sheet. Also, I want to rename them based on their value from Ad Group column.
Each newly created worksheet should have same columns as the master sheet . Same 8 columns with their name intact.
Finally, I want all matching data of the Ad Group values to be placed on their respective newly created worksheet. For example, worksheet A should have A2:H2 data. Worksheet B should have A3:H3 data, and so on.
Please note that same ad group may have more than one row data. But I don't want to create multiple worksheet of same name ad group. The worksheet should be just one, but all matching data should be placed in that one sheet.
I know it is a bit complex task, but I am sure there would be a way to perform this automatically - probably a macro.
View 1 Replies
View Related
Nov 10, 2008
I cant seem to find the correct syntax for creating 14 validation lists using array members as the source of the named ranged. The validation lists are stored on a different worksheet, the Named Ranges are created fine, as are the ranges that are having the validation applied. The Syntax I am having a problem with is
Public Sub assignDVList(WSD As Worksheet, sListName As String)
Dim DVListName As String
DVListName = "DV" & sListName
Application.Goto Reference:=sListName
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & DVListName
It is the Formula1:="=" & DVListName that is creating the headache. The sub is called as the array moves through the columns, using the header row as the Name for the Named Range, and the data Validation worksheet uses the same naming except it has DV in front.
View 4 Replies
View Related
Nov 28, 2006
The "Sum" sheet can change its number of rows. The pivot table is based on it. I'm having trouble with the SourceData portion of the code in my macro ....
View 9 Replies
View Related
Mar 8, 2014
I am trying to create a database that contains data taken from several work books that i have saved in one folder on my hard drive. These workbooks contain quite a lot ** data, but i only want to extract the data i want to instead ** having to extract it all? these works books are used by several other people as well, so i want it to be able to update as data changes.
I was thinking about using data links, but that only seems to extract all the data instead ** just what i want?
View 1 Replies
View Related
Jul 12, 2014
I need a macro that would consolidate all data in multiple worksheets of multiple workbooks in one Master file.
All the workbooks will be in one particular folder. The macro should search for data in all the workbooks and consolidate it in one master excel workbook.
I am currently using both excel 2007 and excel 2010. This macro would really reduce manual work as currently consolidating data from 45 to 50 sheets takes an ample amount of time...
View 4 Replies
View Related
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
View Related
Sep 5, 2007
Ive just started using vba for excel and put together a very simple peice that uses the goalseek function. See below.
Public Sub calc_vol()
Dim currentcell As Range
Worksheets("sheet1").Activate
Set currentcell = Worksheets("sheet1").Range("bl2")
Do While Not IsEmpty(currentcell)
With Application
. Iteration = True
.MaxIterations = 100
.MaxChange = 0.00000001
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
End With
ActiveWorkbook.PrecisionAsDisplayed = False
currentcell.GoalSeek 0, currentcell.Offset(0, -13)
Set currentcell = currentcell.Offset(1, 0)
Loop
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
The code works great (a little slow - but fine for 65,000 lines), however i have nearly 100 workbooks that i have to run this macro for. I was wondering if anyone knew how i could manipulate it so that i could incorporate all 100 files into the macro, therefore running the macro once for all 100 files. If this is not possible is it possible to link multiple worksheets.
View 2 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
Apr 7, 2014
I have approximately 30 workbooks for which i open and run a macro to change the look and feel for client perusal.
I normally open each of the 30 workbooks and run the macro for each one.
Is there a way I can run the macro to apply to all workbooks at once?
View 3 Replies
View Related
Mar 28, 2009
I am looking for a way to close a group on Workbooks that are in separate instances of Excel.
These Workbooks get opened similar to running a Powerpoint program, but much different usage than Powerpoint.
AS AN EXAMPLE:
I have 7 Workbooks open in Excel . . . Could be more or less.
Each workbook has 2 sheets, the "User Sheet" (not the actual name, as all user sheet names vary) and a sheet named "Setup." (Always named "Setup.")
On the "Setup" sheet I have a list of all the possible Workbooks that I want to close. For reference purposes only, I'll call this list "To Close". All 7 Workbooks would be listed, but not all of them would be opened. If any particular Workbook on the "To Close" list is not open, it would simply be bypassed.
Example of list: Column A
Row 25 has the name of Workbook A
Row 26 has the name of Workbook B
Row 27 has the name of Workbook C
And this would continue down to the last Workbook possibly open. I will know how many Workbooks could possibly be open.
Other workbooks could also be open, but they would not be closed by this macro. In this example, I would only want Workbooks A & B & C closed.
I would have rows 25 thru 27 (or more) listed in the same location on the "Setup" sheet of each Workbook.
Each "User Sheet" would have a macro called "Close Group"
The macro would have to search through all open Workbooks and when it found one of the Workbooks listed for closing, it would close that workbook. Then, the macro would look for the next Workbook on the "To Close" list.
The Active Workbook would also be on the list and that also needs to be closed. I would guess that this would be the last Workbook to close, but it really would not make any difference if the macro would still work. I think the macro has to have the active Workbook open to close the others.
After the list has been gone through, the macro would stop.
NOTE: Each Workbook has it's own "To Close" list. If it would help, I can leave the Workbook Name A off of the list in Workbook A. I would do the same on Workbook B, etc. Then the Active Workbook could automatically be closed at the end of the macro.
View 14 Replies
View Related
Sep 14, 2009
I have multiple files with data in that I need to merge, basically append the data from various files to an existing file. Does anyone know of a macro that can be written to do this?
View 10 Replies
View Related
