Produce Numerous Workbooks Based On Filtered Names
Oct 25, 2012
I am trying to produce numerous workbooks based on a filtered name. I will attach a sample spreadsheet that has the data.
On the sheet we have engineer names. I basically want to filter them (not difficult with a macro) but then to copy the results to a new spreadsheet and save the workbook as the engineers name (ie J. Bloggs has 5 jobs so they are filtered and the results are dumped into a new workbook and then saved as J. Bloggs.xls). This will happen for all engineers.
I have though about doing it as a macro and I think that would give me the end result but we have around 20 engineers and these can sometimes go up and down. Is there anyway to do this automatically?
I have created a template workbook for individuals who work on different sites. The spreadsheets used by each individual have the same headers. The sheets all sit in the same folder in dropbox.
I am wanting to produce a summary workbook within the dropbox folder that automatically populates when an individual updates their workbook.
I have attached mock example of the individuals worksheet for your information.
The questions that I have are as follows:
1. Is it better within an individuals workbook that they record their activity all on one sheet as opposed to having a spreadsheet per months activity? It seems tidier to have a sheet per month but does this make it more difficult to produce a summary workbook?
2. The individual is required to record a patients individual drugs on separate lines but they need to record a unique identifier, age, *** and month worked. Do they need to record all this for each line of drug or is there an easier way?
3. How should the summary sheet be produced - pivot table or sheet with formulas linked to the individuals workbooks?
I have a folder with 250 files. Each of the file has only one column. I need to search the rows starting with "Party Name" and copy them in any one row. I want a macro either to copy the filtered results in the same file or a fresh workbook.
Column A row 1 ABCDE row 2 FGHI row 3 Party Name:Abcd row 4 JKLM row 5 nopq row 6 STUV row 7 Party Name:ryz row 8 Party Name:mno row 9 XYZ
I want the results as below:
Column A Column B Column C Column D row 1 ABCDE Party Name:Abcd Party Name:ryz Party Name:mno
I used the code below to successfully create workbooks from the filtered list using the below code provided on this forum (see link below). The only problem is that I want the resulting columns to fit to width.
Looping Through A Range - Use Result As Criteria In A Filter
I was trying to use an if statement to produce results based on 2 cells. The number in the 1st cell can be the number 1 to 5, but each number has critera with it. Say the cell is A1 and i enter the number 1. Then i want to check the value of Cell A5, if this is less than 40 then the cell A10 should read 250 and if A5 was over 40 then it should read 285. struggled to get a formula properly to work.
Here are the values for each number: A1 = 1 with cell A5
I have a workbook that has three worksheets. The ASU Database sheet contains part numbers and descriptions including specific details that are selected from pull down data validation lists that are named on the third worksheet. I want to have a front end on the first worksheet that has similar data validation lists for the user to select from to create a search criteria that will create a worksheet with only the rows that meet this criteria. I am not sure what method to use to accomplish this.
I am currently overhauling a massive workbook, that previously relied on an absurd amount of named ranges in order to run vlookups. I no longer need these named ranges and would like to delete them to unclog the workbook. Currently I only see a way to delete them one by one. Is there a way to mass delete all of them?
Each month I run 2 separate reports and have macros that break each report out by distributor. The end result is each spreadsheet creates a new tab and new workbook for each distributorís information. Each spreadsheet has information for 30 distributors, so I end up with 60 new tabs and 60 new workbooks.
Example: Spreadsheet one separates detailed sales information for: Abc Company 123 Company Misc. Company
Spreadsheet two separates summary sales information for: Abc Company 123 Company Misc. Company
Currently, I save each spreadsheets new workbooks in a different folder because each spreadsheet creates workbooks with the same distributor name. I then go back and open each spreadsheet with the same name and move a sheet from one workbook to the other and resave. Now I am trying to figure out the best way to merge these back together and am struggling with where to start.
I could keep opening each of the 2 new spreadsheets that are alike and copy or move sheets but with 30 new tabs to move to the matching spreadsheet it becomes time consuming. Iím hoping thereís a better way!
Iíve attached the Sales Detail Example and Sales Summary Example to see how the spreadsheets are being broken out but had to remove some data to trim down file sizes.
In one workbook, i have a sheet with date like this:
In each case, the Data is a block of cells of varying number of columns and rows. Each of the data blocks is a named range. When I reference the ranges from a separate workbook, the results from calling the range don't work at all (return #VALUE!) UNLESS the layout of the second workbook is the same as the source data! so for example, if range Data is in cells A2...F5, then i can reference that data ONLY if i put my formula in cell A2...F5 on the second workbook. this appears downright ridiculous to me and i'm hoping someone can tell me what i'm doing wrong!
I tried recording a macro to automate some tasks I perform every day between two workbooks. While in one I want to go get data from the other. The problem I'm having is the name of the workbooks I want to run this on changes every time I download my data into Excel. The files always start with the same name but the date gets appended to it. Is there a way to modify the macro to just work with the two books I've got open?
I've named some cells in the source workbook, and linked these names to the destination workbook. Then I used the names in some formulas in the destination workbook, and it worked great. But when I closed the source workbook it doesnt work. WHen I open the source it works great, but I need for it to be able to link to the workbook when it is closed as well.
I would like to create a summary for the ordering history of each customers. The IT department will facilitate us to generate some raw data and I want to retrieve the data to the summary excel when I type the Ref No of the customer.
For example, I have the following raw data generated, in which the file name is "A123456":
Ref No Name Address
And I want to extract the data to the following summary. When I type "A123456" in the field "Ref Number" in this summary, it will automatically retrieve data from the corresponding raw file:
Every month I work on an audit that has data from 35 different distributors. I have code below that puts each distributors audit/sales information on a new tab and each new tab is given the distributors name. This also creates a new workbook for each distributor.
When the new workbooks are created, how can I name each workbook with the distributor name it's being created for? Is it possible to predefine a file path to where these new workbooks will be saved?
The distributor names I'm using are in column AF.
Sub FormatList() 'The code below creates and names a new tab for each members info Dim ws1 As Worksheet Dim wsNew As Worksheet Dim rng As Range Dim r As Integer Dim c As Range Set ws1 = Sheets("Sheet1") Set rng = Range("Database")
'extracts a list of member or distributor names ws1.Columns("R:R"). AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Range("T1"), Unique:=True r = Cells(Rows.Count, "T").End(xlUp).Row
'set up Criteria Area Range("U1").Value = Range("R1").Value
For Each c In Range("T2:T" & r) 'adds the member name to the criteria area above ws1.Range("U2").Value = c.Value ...............
I have data spanning many files which are named with the date on which they were created (so there are 31 files for August). For example: PL080107, PL080207, PL080307, PL080407...)
I'm trying to compile all of my data into one workbook, and have the macro to append each file to a list. Now I need the macro to either OPEN each file, or ACTIVATE each file so that the rest of the Macro can grab the necessary data.
I say Open or Activate, because I can MANUALLY Open a full month's worth of files if it's easier code. If not, I would like it to open and close each workbook on its own.
I have over 7 month's worth, so opening 31 files 7 times is better than opening over 200 files individually!
I currently have a piece of code that opens all of the files in a folder that are called "*agent*", opens them and copies information. Now, these files come with numbers at the beginning which, are always the same. I only want to open certain files that begin with, for example, 801, 802, 803, 804, 805 and 806. How would I write this into my code? As you can see from the below code, it now looks for the files that all have "agent" in the name, but this is opening files that have that name but are not the right ones. Here is my current macro...
I hope this isnt as simple as putting "MyFile = Dir(MyFolder & "*801*", "*802*")" etc.
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.
this may or may not be easy for some of you but its driving me nuts. Here's what I need to do.
1.Create a new workbook
2.Cycle through multiple workbooks and all worksheets within each workbook (all in the same folder). All workbooks will have the same structure.
3.Take the worksheet names and put them in rows
4.Take the data from the first column (column A) from the first worksheet of the first workbook and put them across the top (i.e. transpose the data). The first column will be the same in all workbooks so it doesn't matter where I get it from.
5.Copy the data from column E from each worksheet and paste them in rows in the new workbook (again transpose) corresponding to each worksheet.
6. Perform simple mathematical calculations at the end of each row.
I expect to have approximately 26 workbooks with a total of 7000 worksheets. In the target workbook (i.e. the new one), I expect there to be 7000 rows (corresponding to the 7000 worksheets) and about 260 columns.
The only reason I need to transpose stuff is because Excel 2007 doesn't have 7000 columns.
Here's an example of what it would look like. Example worksheet (Input)- Worksheet ABC Col A Col E 1/1/2004 $25 1/8/2004 $30 1/15/2004 $15
Imagine another worksheet called LMN with the same ColA but different values in Col E.
Output workbook ColA Columns B Column C Column D Sheet 1/1/2004 1/8/ 2004 1/15/2004 ABC $25 $30 $15 LMN $xxx $yyy $zzz
I have a workbook with multiple worksheets. First 4 tabs are the standard tabs and rest of the tabs are created based ona macro with the unique names. Now i want to create the separate workbook for each tab by its name and , date and time stamp in a C directory.
I am trying to create a spreadsheet that will take all my grades from the semester (I'm a college student) and compute my GPA. I managed to get a weighted average for each class, but the grade is on a scale of 1-100. I need to convert that to a letter grade, and then a 4.0 scale. How can I set it up so that it will return "A" if 90<Grade<100, "B" if 80<Grade<90, etc.? I'm apologize if this is something simple, but I've been trying to find the right function for a long time.
I work for a construction company, Someone else created a workbook a few years ago with 140 similar pages, each listing a separate piece of equipment as a service record. I need to print these out randomly for mechanics to update with new information, as they service the machines. However, some of the pages of the original workbook are goofy and the workbook as a whole could be improved. But, oh that many pages!
I have worked on a new template page for entering information to make the worksheets all cohesive and updated. Is there an EASY and/or FAST way for me to copy this page 150x to make a new workbook, and just enter my data onto each new page? I really don't want to reformat each page of the original workbook individually to see if I can fix all the glitches and weirdnesses that have occurred on the pages over the years. I thought it would be much easier to retype the data onto new pages.