Automate Advanced Filters With Macro
May 29, 2007
I need to use the Advanced Filter tool to allow the user to filter (in-place) the Guests worksheet while providing the following summary stats: total revenue, ave. revenue, max/min revenue and total number of tours ie the user should be able to enter any criteria below the database to to show only those records (and summary stats) that satisfy the criteria.
To automate the operation of the advanced filter tool, I need three macros. "SelectRoom" and "SelectGuest" macros should prompt the user to enter a value through an input box to filter the data according to a client's name or language tour. The third macro, called "ShowAllGuests" should clear the criteria row and dispay all clients in the database.
The first two macros should include an error message to prompt the user to rerun a macro if no clients satisfied the criteria while the "ShowAllGuests" macro should include a message box statement at the end to display a short message giving credit to the macro author. These macros also require a button each (three in total) in the Guests worksheet!
View 3 Replies
ADVERTISEMENT
Jun 7, 2006
I have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters.
View 5 Replies
View Related
Jul 10, 2009
I am having some problems trying to filter a list to display exactly what I want to see. The list has one column of part numbers, a second with due dates, and then another with quantity.
I want to use an advanced filter on the part numbers to only look at unique entries. Then I want to filter that list using a custom filter on the due dates to only view those due within a certain period. So ultimately I want to view only unique entries due during a given period.
I am able to apply one filter, but when I go to apply the second, the second filter removes the first. For example once I have filtered out duplicates, when I try to filter based on date all of the duplicates return.
View 7 Replies
View Related
Apr 27, 2009
using VBA I have a control sheet which summerises variouse counts & totals of data held on a detail sheet. Bu using filters and counting the visable rows.
Statistics on 50 columns of data held in several thousand (rows) mixed around eight business regionsheld in first column.
I could determin the number of affected rows by using Tick boxes on the control sheet and applying filters to the detail records
A) checkbox indicates if I need filtering on the type of data in my detail sheet and apply the filter
Selection.AutoFilter Field:=XX, Criteria1:="Y"
B) Because I could not have more than two criteria on an autofilter column I resorted to using Advanced Filter on the column with the Business UNIT's,
I Create a range write the criteria of the records to be filtered into the range, then apply an advanced filter using that range.
Both of these work well indevidually, but I am getting inconsistant results when I mix them
using the autofilter route I can select multiple tick boxes and the output is correct, and using the advanced filter I can select any combination of business units and the output is correct, however I cant get them to work together
View 4 Replies
View Related
Sep 17, 2008
I can't seem to get the hang of advanced filters with multiple criteria. BTW, I have no problem solving for a single criteria. I've found many instructions on how to do it, but zero specific syntax examples, and I'm obviously doing it wrong. For example: ....
View 9 Replies
View Related
Sep 8, 2009
Basically I have set up a list on sheet1, this is now on a drop down in sheet2 and my raw data in sheet3.
I want the table in sheet2 to just display the product items I select in the dropdown but it is displaying all the raw data (although is updating when I update the raw data)
Granted I copied the code whilst researching but I'm just not quite there.
Could someone possibly look at the attached and let me know where I am going wrong. Also as you can tell I am very new to teh VB application in Excel, could anyone reccommend a good place to start learning the very basics?
View 9 Replies
View Related
Jan 17, 2009
I need to lookup and concatenate all the header values where the value in the corresponding row equals a certain value. For example, column D, has the column header value if the cell values equals "X". Likewise column E has the column header value if the cell values equals "Y". I have too many columns (this is just a simplified version) to use nested if statements.
resource1resource2resource3 XResources YResources
Jeff X resource2
John X X resource2 resource3
Jim Y resource1
I'm not an excel newbie, but I keep thinking there must be a simple solution that I am missing.
View 6 Replies
View Related
Jul 4, 2014
I'm creating a database that contains a columns of hyperlinks and background color.
I've created code on Excel 2010 with advanced filters to copy selected rows to another worksheet. The extract works fine and those cells with hyperlinks and various color codes from the original database "appear" to have been extracted correctly.
However, a closer look shows the hyperlinks on the target worksheet are just blue underlined texts...with the links no longer working. Is there a way for me to correct this?
View 9 Replies
View Related
Jul 18, 2014
I have created a spreadsheet that will show me where people are working on what day, etc. however i want to be able to filter by week to create a list of say 2 particuar shifts - in this case "syl ld" and syl n so that the spreadheet would show the people who are working these shifts and I could print out. I have tried Multiple Filters and Advanced search but cannot achieve what I require
View 3 Replies
View Related
Jan 20, 2010
I am using Excel 2007. I have a list of 100 names all ranked from 1-100. Is there a way to create a macro that can filter out the top 20, a specific name and also any names with the cell colours blue and yellow?
At the moment using an advanced filter based on criteria I can filter out the top 20 and the specific name I want but can't work out how to leave the coloured rows in as well.
View 3 Replies
View Related
Jun 20, 2014
I have a pivot table with various filters, and I would like to setup a macro assigned to a button which resets the filters to 'All'. Used this code to clear one field and it worked successfully:
Code:
Option Explicit
Sub ClearFilters()
'
' Clear Filters
ActiveSheet.PivotTables("FLIGHTS") _
.PivotFields("MONTH").ClearManualFilter
End Sub
The question that I have is: how can I list the other fields in the same macro, or is there a command that just resets all available field so they don't have to be listed individually?
View 4 Replies
View Related
Jun 23, 2014
I am working on a complicated macro. The macro is suppose to move certain data set to a specific worksheet if an if statement is "True."
As of now the macro, inserts the row into a new sheet but it is inserted into Row 1. My data set is in Sheet A is up to Column U and I want it to be inserted in Column B on Sheet B. The code below inserts the entire row into Sheet B starting with column a.
[Code] ........
This variation of the code moves the entire data set from Sheet A to Sheet B and inserts it in Column B as wanted, but it does NOT follow the Criteria of "true", it brings the entire data set even those with "False".
[Code] ..........
View 1 Replies
View Related
Feb 28, 2007
I have a mixed list of materials in column 'A'. I have two macro/filters to extract (copy/paste)... one for those ending in "[G]", and one for all others (those not ending in"[G]"), to columns 'B' & 'C' respectively. The code looks like this;
Sub Filter_G()
Range("B2:B" & Range("B65536").End(xlUp).Row + 1).ClearContents
Range("A2:A" & Range("A65536").End(xlUp).Row + 1).Select
Selection. AutoFilter Field:=1, Criteria1:="=*[G]"
Range("A2:A" & Range("A65536").End(xlUp).Row + 1).Select
Selection.Copy
Range("B2").Select
ActiveSheet.Paste
Range("B2").Select
Selection.AutoFilter.......................
View 5 Replies
View Related
Jan 27, 2010
hello honorable excel profesionals.
here is what i want the macro to do:
must start from the top of the colum and count cells with specific data in them:
if ''X'' or ''Xa'' or ''Xb'' -> start counting how many cells in a row have the value specified.
from here two things can happen:
if a cel with a value ''Y'' is met -> stop
if a cell with value '''Ya'' or '''Yb'' is met -> countinue counting untill Y is met
when I say stop I mean that the macro should imput, ona separete table:
1.total number of how many counts where made.
2.how many counts there where with a lenght of 1, how many with the lenght of 2 and so on
here is an example of data line and a table I want to have after macro is done.
Hope that this isn,t a problem to write otherwise il have to do this manualy - about 5000 rows of data...
View 10 Replies
View Related
Aug 3, 2007
I have a difficult one I think? I have a workbook in Sheet 2 I have a page with data.
I would like to create a Sheet 3 that would look at Sheet 2 and copy all rows (Range A to V) with the asterisk displayed in Column B... IF MORE THAN 30DAYS AFTER THE DATE ENTERED IN "Column C"....
View 9 Replies
View Related
Sep 11, 2013
I have worked out how to get my macro behaving the way I want however now I would like to automate the whole process. Basically I would like the macro to work its way down a column (in this example column F) until it has completed and there is no more data in the row before the last.
Here is a code snippet of my macro
Code:
Keyboard Shortcut: Ctrl+a'
If ActiveCell = 2013 Then
ActiveCell.Offset(, 2).Range("A1:E1").Select
Selection.Cut
ActiveWindow.SmallScroll ToRight:=24
ActiveCell.Offset(0, 24).Range("A1").Select
ActiveSheet.Paste
End If
[code]......
View 1 Replies
View Related
Jan 30, 2009
the following code filters data and copies to worksheets specified in code, can this code be shortened to make the macro run faster. Also in my second criteria how can I put "Contains "PT2" in the criteria as nothing happens when i run the macro, is *PT2?
With shtRec
.AutoFilterMode = False
.Range("A7").AutoFilter Field:=8, Criteria1:="LEHMAN", Operator:=xlAnd
With .AutoFilter.Range
On Error Resume Next
Set rng = .Resize(.Rows.Count - 1).Offset(1).SpecialCells(12)
On Error GoTo 0
End With
If rng Is Nothing Then
MsgBox "No data to copy"
Else
rng.Copy shtLehman.Range("A6")
End If
.AutoFilterMode = False
End With
View 9 Replies
View Related
Dec 19, 2008
I am desperately seeking a better way of transposing a large amount of data. By the looks of other posts, my objective is possible with a bit of VBA. Sadly, I am VBA illiterate.
My data looks like this (simplified) [ data is separated by columns "|".
A | May 1 | Jun 25 | Aug 9 | Dec 12
B | Apr 1 | Oct 25
C | Jan 6 | July 7 | Nov 11
I want to make it look like this:
A | May 1
A | June 25
A | Aug 9
A | Dec 12
B | Apr 1
B | Oct 25
C | Jan 6
C | Jul 7
C | Nov 11
I have accomplished this in the past, but it involved importing the table into access, then creating a query for each of the "date" columns, then copying and pasting the results into one table. It was a painful experience considering there are over 2,000 lines on the original table (resulting in 25,00 lines in the combined query).
View 12 Replies
View Related
Mar 24, 2007
Having big problems getting an advanced filter macro to work on-going (meaning again and again and again once I update the table it filters from).
Macro is as follows:
Range("A1:O1").Select
Range("A3:O65536").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"H1:H2"), CopyToRange:=Range("T1:AH65536"), Unique:=False
Range("A3:O65536").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"G1:G2"), CopyToRange:=Range("AM1:BA65536"), Unique:=False
Do I need to specify the sheet that this filter should occur on? If so could someone indicate how I would get that into the macro?
View 9 Replies
View Related
Jul 10, 2014
I have got the general principles of macros and VBA downpath.
Excel Example.xlsx
The document I'm working with is confidential, so this is an example. Basically, I'd like to string together the content of all cells from the second column associated with each company in the first column (separated by comma + space). In the original document, the companies are located in B2 to B8486, and the key words are located in D2 to D8486. My example uses ampersands, but I seem to understand Concatenate would yield the same results.
View 9 Replies
View Related
Feb 15, 2014
I am trying to write a small macro involving Len and Left formulas. This will take input from two cells and and give the value on a third cell. I know how to do this on excel. This goes as follows:
Value on A5 = HB_AM_1
Value on B5 = 1
My formula to extract the specific text =LEFT(A5,LEN(A5)-LEN(B5)-1).
My result on D5= HB_AM
Here, my inputs are clearly defined and I can write this formula on cell D5 to get the result. I can copy this down to the end of my data and I can get my result.
My challenges: I need to do this for various reports where the data extends to various rows. I think looping will solve the problem, but not sure how to do it. The value on cell B5 will always show the last number at the cell A5 (1 in the eg above). So, if the number in A5 is 10, value in B5 will also be 10. This is already there in my data, so I do not worry about this part.
View 5 Replies
View Related
May 20, 2008
I have a stock sheet of about 1500 items that is printed every 3 months for stock take.
The changes are then entered manually. I have set up a stock order sheet using VLookup and manually enter the items that need to be ordered. I'm wanting to know if i could use a macro to do this automatically and check only for the stock items that have changed (ie. items have been sold ). Stock levels needed on hand are usually constant but is changed from time to time so will have to be taken into consideration. I have included an example worksheet which would better explain what i have been doing so far. Also if possible could the macro generate a new worksheet for the order sheet and name it the corresponding date and month of generation.
View 11 Replies
View Related
Oct 6, 2008
I have a macro in Module1 of workbook "BAU" called "ExtractToSheets" which exports data depending on the contents of a cell (Thanks royUK et al)
At the moment however, you must run the macro manually to export the data.
Now, i have put some simple validation in column K of worksheet "BAU Data" within the "BAU" workbook. Whenever a cell within a predefined range with column K is changed to "Yes" i want the macro to run AND i want the row the cell is in to be locked so it cannot be edited further by the user. I have had too many users mess up previous sheets i have made to collect my data, and i wish to make this one somewhat more idiot-proof!
Any pearls of wisdom folks?
I would have attached a representation of my sheet, unfortunately because there is now a macro in my spreadsheet, our trigger-happy security software won't let me post it!
View 14 Replies
View Related
Oct 27, 2011
I am running Excel 2003.I have a spreadsheet with two worksheets, each worksheet has a Refresh button that runs a Data Extract Macro.The Data Extract Macro imports Data from another spreadsheet.I want to have the spreadsheet automatically run my Data Extract Macros for both worksheets at a specific time each week. (Scheduled task) Regardless of whether or not the excel file is open.
View 4 Replies
View Related
Oct 4, 2007
I've recorded a macro which selects "1" in a filter drop-down box then prints some pages, then comes back and selects "2" in the filter then prints some pages, etc etc etc.
I've looked at the code for this and it treats the "1", and the "2" etc as text each time - originally I had the filter on names, but the names change each time I need to do this whilst the process doesn't, so I filtered on numbers instead.
However, I would have to enter the repeated blocks of code down to 1000 to get it to select down to "1000" in the filter. About 3/4 lines each time but with me manually typing in 1, 2, 3, 4 .... 999, 1000.
Is there a way to say in VBA 'repeat filter selection until you've run out of numbers then come back to "All" and stop ?
View 9 Replies
View Related
Oct 1, 2008
Here is my scenario:
4 step process or more?
1)I have a text file (with delimiters) that I want to be copied over to an existing workbook (because of the header)
2)So now I have a temporary workbook that includes the header and the data from the text file. I want this temporary data to be transferred or copied to a sheet in another workbook(s), wipe out previous data and store the new one. Save the workbook(s) and close.
3)Close temporary file. Don't save.
4)I want this process to be automated -- run it daily everyday at say, 6:30 in the morning.
View 9 Replies
View Related
Aug 22, 2008
I have a pivot table with over 4000 entries and about 12 columns which i filter to get the info i want.
Unfortunately with so many rows and columns in the table, if i do a macro that gets each filter back to 'show all' its really slow.
Doing this manually using the "Show all" option in the filter takes a fraction of a second, so surely there must be a way to do this more quickly in VBA?
i can't understand why manually it takes no time at all, but automating it through recording a macro takes forever.
View 9 Replies
View Related
May 29, 2013
I've got an old Excel sheet with Stephen Bullen's function for returning the active filter criteria (Rob on Programming: Excel: Displaying Autofilter Criteria). My status sheet may be filtered in multiple ways, and when the user is happy with the filter selections, she can create a powerpoint file with a graph and a summary of the filtered table. As we are using Excel 2010, users are very likely to select more than two filters.
Example: Range A1:E100 has the following headers: Field, Installation, Project,Type, Phase.
The controller wants to filter on:
Field equals north or south or west Phase equals completed
The manager for Field South wants to filter on:
Field equals southType equals maintenance or repair or modification Installation begins with Zeus.
As the filters are not shown when I copy the table to powerpoint, I would like to create a summary of the user's active filters that is pasted into a sheet (for subsequent copying to powerpoint). For the users in the example above, that table would look something like this:
Controller:
Active filters
Field: north, south, west
Phase: completed
Manager, Field South:
Active filters
Field: south
Type: maintenance, repair, modification
Installation: Zeus*
I've looked at various functions intended to take Stephen Bullen's code into Excel 2010's multiple criteria world (e.g. this: User Defined Function to Display AutoFilter Criteria for More Than Two Criteria in Excel 2007 / Excel 2010), but I have not been able to convert it to a functioning macro.
Any code that could be used for this sort of task, or any tips for relevant code?
View 2 Replies
View Related
Feb 15, 2013
I've written a macro in an attempt to automate the advanced filtering in Excel.
VB:
Sub FilterData()
Sheets("Filter").Select
Range("B10").Select[code]....
View 5 Replies
View Related
Jun 28, 2008
This is quite a easy one. I am a begineer so I don't know how to do it. I keep getting a runtime error. I want create a macro that does the following. I have this data(it is made up).
NameSchoolFee
JohnFDU700
MarkeyNYIT400
NickNJIT500
SamBU100
I want it to autofilter school njit
NickNJIT500...........
What would be the code for this? I want use the code for bigger data?. Also if you have a file which is 100 lines long and your macro does a series of steps for that file. Then you have another file and you want the same steps done but it is 500 lines long. How would you write a macro thatrun the steps taking in the last line and not just a 100 lines.
View 2 Replies
View Related
