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 would like to know how to create a validation based on a list, but with filtered. I have an employee sheet and another sheet to select the names but only according to a specific job.
View 7 Replies View RelatedI have a workbook (see attach) That has 2 worksheets. (LOAD DATA AND SKIP) What I would like to do is have VBA that automatically filters for each of the names in column A, creates a new workbook naming the workbook as the filtered name (ex D Fowler) and current date and then copy in all info based off filtered data from columns A thru Y Then save and close for each of the names in column A.
View 6 Replies View RelatedI have a sheet of data that I can filter (e.g. only show male pupils).
On the next sheet I want to display the list of filtered results with no gaps.
As the user will be able to select which column of data to show from the first sheet I am referencing it as follows:
INDIRECT("'Progress Matrix'!"&ADDRESS(MATCH($B$6, 'Progress Matrix'!$A:$A, 0)+1,
MATCH(C$6, 'Progress Matrix'!$1:$1, 0))&":"&ADDRESS(300, MATCH(C$6, 'Progress Matrix'!$1:$1, 0))))
(However this formula no longer seems to work as I think I changed something).
I've tried to incorporate
SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0))
to only show filtered results but with no success.
Progress Tracker.xlsm
make a list of all Excel Workbooks in specific folder(My Documents). And once clicked, it will open the file immediately.
View 10 Replies View Relatedcreate a list of all open workbooks? I've found various examples of code that will do this in a Message Box, but what I am after is for them to be listed actually in the worksheet (let's say in column AA).
View 5 Replies View RelatedEvery 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 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?
So I have a guest list workbook. There are two sheets. On the first one is a list of names on Column A. On Column B is a classification: 'C' if confirmed; "D" for declined; "I" for pending. I want to have all the names with "C" on sheet 1 appear on sheet 2 automatically.
View 4 Replies View RelatedI am using Excel 2007 and I would simply like to autofit every column o that the information fits and is visible. I would do this myself, but when you have to open many files with as many as 100 columns, its not fun.
View 2 Replies View RelatedI am trying to program a macro to AutoFit 29 columns on 3 different sheets. However it keeps coming up with "run-time error '1004' select method of range class failed". Here is the code
[Code] .....
I have an excel workbook with 7 worksheets. I am wanting to autofit columns (A:G) on the last 5 sheets using VBA.
View 3 Replies View RelatedI am trying to write a small sub which will make do an "AutoFit" for all columns of the Active Sheet. For example, the code below does an "AutoFit" for columns A:J of a sheet. How can I make it do the same for all columns (not only for columns A:J) of the ActiveSheet?
Columns("A:J").EntireColumn.AutoFit
I have a worksheet that has several hidden columns and wish to have the EntireRow.Autofit apply to all rows in the current region based on visible columns. After using the WrapText method on all visible cells, one of the hidden columns with a lot of characters causes the entire row to be much larger than desired. Is there a way to use EntireRow.Autofit to only expand the height of each row in the current region as necessary based only on the visible cell contents in that row?
Set AllCells = ActiveCell.CurrentRegion
ActiveCell.CurrentRegion.Select
With AllCells
.Font.Size = 6
.Font.Bold = False
.WrapText = True
.EntireRow.AutoFit = True
End With
I'm trying to not have to go through every worksheet in my large workbook to autofit the columns in each worksheet to my cell content.
View 4 Replies View Relatedthis 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
My data is set up as
A
B
C
1
A
B
C
[Code]....
How Can I make that data appear as
A
1
A
2
B
[Code]...
Where is takes the row and keeps transposing it down 3 lines at a time?
So far I created code that creates a Distinct Unique item's list for one column. It works as it should but I want to modify the code to create a Distinct Unique item's list for two columns that are related to another i.e. a person's name to their license #.
Here is the code below that works for one column only:
[Code] ......
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
[URL] ......
In that file, you can see there is a list of somewhat similar data across 5 columns. Some of the entries are duplicates, some are different by only one character. What I want to be able to do is generate a list of all the unique rows of data, taking into account the data from ALL 5 columns. The list can be on another sheet or simply just further down on the same sheet, that doesn't matter too much.
I could do it if it were just one column of data, but struggling trying to do it with several columns.
I have a spreadsheet of 12 columns (A to L), with a list of student names under each down to row 31. Each column represents an after-school club that the child can attend.
However, a child can sign up to multiple clubs, so their name can appear in several places across the spreadsheet.
What I would like to do is have a 13th column which is an overall list of students who have signed up to at least 1 club, i.e. their name appears at least once in columns A to L.
How do I achieve this so that only unique entries are listed and duplicates are removed in this 13th column?
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?
Test Cost report 1.xls
I am trying to create a straight column list that can take the rows and columns of a table, and list only the nonblank items. The formula I am using only seems to work with one column, not multiple.
Formula:
[Code] .....
I have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.
View 5 Replies View RelatedI am trying to sum a filtered list and when a filter changes I want the sum to change and it isn't right now. I have attached a TEST workbook in which all the data is values but in my situation all of the data is actually read from a workbook from each agent.... not sure if that matters.
If I filter by District (East or West), how would I get the total to change? I am trying to avoid any vba or macro within this workbook.
I'd like to have a dynamic report that ranks my lists based on an autofiltered list. In other words, I'd like to toggle the criteria that qualifies the lists content, and have excel generate "ranks" based on the values returned. Currently the rankings are based on the overall list, includeing the records that are filtered out.
View 4 Replies View RelatedI am trying to write a macro that will let me loop through an auto-filtered list (column C in example file) . I want to filter for a criteria (in this case a network), copy the filtered data, paste it onto a new workbook and save it. Although I have been able to record a macro (see below) that can do this, I have to copy and paste this macro over and over again for the other networks. I would like to know if there is a macro that can loop through each criteria in an auto-filter list, copy and save the data into new workbooks for me. I've attached a sample workbook that should be helpful in understanding what I'm trying to do.
View 11 Replies View RelatedI have a list 10 of names in Column A. ( On sheet 1 )
I then filter column A to show only three specific names, or four names etc. . .
On Sheet 2, in cell A1, I would like to list the three filtered names from column A on sheet1
e.g. if the three names showing in column A ( Sheet 1) are: john, mary, jane . . . then on sheet 2 cell A1, I would like to see: "john, mary, jane"
I need to apply a dynamic filter to my constantly changing list.
Here's an extract of the fields in Sheet "Materials". I need to filter by an of the Type Levels or by the "Index #" fields. The result would be used in another sheet. The return value should be a filtered list from the "Full Material Name" field.
Material IDIndex #Type Level 1Type Level 2Type Level 3Type Level 4Material NameFull Material NameM0000110010Class 0 - Raw MaterialsSection 0 - Metallic MaterialSheet & PlateGalvanized10gSheet & Plate - Galvanized - 10gM0000210010Class 0 - Raw MaterialsSection 0 - Metallic MaterialSheet & PlateGalvanized12gSheet & Plate - Galvanized - 12g
I'm trying to write a macro that goes through every value in a filtered list.
Specifically, I'd like the macro to select the first value in the filter. Perform the operation. Select the second value in the filter. Perform the operation. Select the third value in the filter. Perform the operation. Etc...