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
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.
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.
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.
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.
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
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.
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
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.
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.
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".
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"
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.
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.
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.
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
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):
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.
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.
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?
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.
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.
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
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.