Automatically Refresh AutoFilter When Cell Value Changes
I have a sheet named "Risk Register" and another named "Outside Residual Risk Threshold". I need an event macro on the worksheet "View code" section, so that any time a value changes in column AF on "Risk Register", the filter on rows 8:39 in "Outside Residual Risk Threshold" is refreshed. Both sheets are protected.
The code I tried in the "view code" or "Outside Residual Risk Threshold" was as below...
View Complete Thread with Replies
Related Forum Messages:
Automatically Refresh Autofilter
I have a worksheet set with Autofilter. This worksheet is populated by formulas taking data entered on another worksheet.
I want the Autofilter for the first worksheet to automatically refresh each time I view that worksheet or as data is entered on the supporting worksheet.
Refresh AutoFilter Automatically When Any Change In Filtered Column
I read your reply to the thread below, and used your Worksheet_Calculate routine from it. It only works for the first sheet in my workbook. I have multiple sheets in the workbook, and when I run the routine on an activesheet other than the first sheet in the workbook, it doesnt work.
Is there a way to get this routine to work on a worksheet other than the first one in the book?
Refresh Autofilter If Column Changes
I have an autofilter set up that hides all rows with 0 in the first column. This information is automaticaly filled in from information on another sheet. Using a worksheet code from a previous thread I have got all the worksheets to automatically refresh which is great.
Private Sub Worksheet_Calculate()
If Me.FilterMode = True Then
.EnableEvents = False
. ScreenUpdating = False
.CustomViews.Add ViewName:="Mine", RowColSettings:=True
Me.AutoFilterMode = False
The issue I have is that there are 52 worksheets (it's a weekly rota) and if any change is made anywhere on the sheet, all the worksheets then refresh which takes just over a minute (about 40 changes need to be made each day).
Does anyone know if there is a way that the information will only refresh on all sheets if changes are made in colums A? as this is the only information that is taken through to other sheets. Or is there another way of doing this?
Auto-Refresh AutoFilter With Sheet Protection On
I found one of your old VBA entries that allowed me to automatically refresh an autofilter function, and it works great.... with protection off.
As soon as I protect the sheet so users can't enter data into the pages showing the autofiltered data, the autofilter kicks off.
i have set the protection to allow autofiltering, so it's something that is preventing the macro (for automatically updating/refreshing the autofilter).
I'd love to either fix the macro to "autorefresh autofilter" with protection on, or just enter another code that will disable all functionality on the worksheet. (workbook is set up with multiple sheets, first sheet is for all data entry, subsequent sheets are filtered data-trying to make them viewable only).
Refresh The Pivot Automatically
I have a pivot table being created from multiple sheets in a workbook. This pivot table is created in the same workbook. Now how do I refresh this Pivot sheet automatically when any of the worksheets are updated. Even manually I am not able to refresh since the Refresh data menu is greyed out.
Automatically Refresh A Sheet Every Hour
I have a workseet that is basically a massive list of orders that have been placed. I have some conditional formatting based on cells that use the today() function to work out how long (in days) since the order was placed.
Trouble is, the spreadsheet is always open, and is never closed down... so it does not refresh automatically. So orders that should go red to indicate a week since they've been placed, don't!
I don't want to rely on people refreshing the screen. Ideally, I would like it to refresh once a day at 1 minute past midnight, or even every hour...
Automatically Run Macro After Pivot Table Refresh
I have a Pivot Table feeding a chart that needs specific formatting
However, after a Pivot Table refresh, the chart loses any custom formatting and returns to the default formatting.
(I know I can create custom user-defined charts but I don't want to do that here)
What I would like to happen is for a (chart formatting) macro to automatically run after a pivot table refesh occurs. It should only occur on the one worksheet that contains the Pivot Table and associated chart and should run the macro after a refresh REGARDLESS of whether the data within the table was updated or not.
Refresh/Recalculate Custom Color Function Automatically
I have this spreadsheet with tons of vba coding. The intent of the spreadsheet is to track invoices as it goes through the approval process. The part that is giving me issues is the first and seventh column. The seventh column is actually a formula that returns how many days between the invoice due date and today’s date. The coding works fine, with one small glitch. Once the dates have been entered, the first column (which contains the color-coding) stops changing colors. What it should do is anything with 0 or less days remaining should show red, between 1 to 10 days, show yellow and anything above 10 days, show green.
So if I enter an invoice with 11 days remaining to pay, it will show green in the first column, but when I open the spreadsheet tomorrow, it remains green, even though it should show yellow.
What I want it to do is refresh every time it is opened, or every day, which ever is easier. There are reasons that we cannot use conditional formatting, one of them being that we want to expand the color coding system to more than 3 colors soon, but I want to get this working correctly first.
This is what I have. I cannot attach the spreadsheet because it contains confidential information.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim h As Integer
Dim i As Integer
Dim j As Integer
Dim lastRow As String
Dim fRowValue As String
Dim gRowValue As String
Dim mRowValue As String
Dim rRowValue As String
Dim uRowValue As String
Dim paidCheck As String
Dim stringAnswer As String
Dim testAnswer As String
Dim testDate As String
Application. ScreenUpdating = False
lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
For j = 8 To lastRow
If Not Intersect(Cells(j, 2), Target) Is Nothing Then ...................
Automatically Refresh Pivot Tables After Data Entry
This is the first time I’ve tried to use VBA. I’m using Excel 2000. In my excel workbook have 1 sheet called “Cards” in which I Change data in one cell $B$2 (enter a part number) and formulas in this sheet return many values from another sheet “Card Data”. In the “Card Data” sheet, formulas that look through a sheet “Sales Book” and return the data that pertains to the Part Number I entered in the sheet “Cards”. In the sheet “Card Data” there are 3 pivot tables (“PH CALC”, “PH QTY”, and “Pivot Table 3”) that use the Part Number data to show 3 different sets of information. The pivot tables are the source for 3 charts in the “Cards” Sheet.
Problem: Pivot tables don’t automatically refresh. I would like to use VBA code to automatically refresh the pivot tables in the “Card Data” sheet when I change the Part Number in cell $B$2 of the “Cards” sheet, which, in turn, would then update my charts in the “Cards” sheet.
Autofilter Updates Automatically
I have two worksheets, one that I update information on and the other which just basically points to that information. The second one is for viewing by a different department and has an 'Autofilter' applied so they only see certain information. This works fine as such until the data is changed in the first worksheet, at this point the 'Autofilter' doesn't automatically update, I have to take the filter off then put it back on again.
Is there anyway to ensure the autofilter updates automatically or is there an alternative way of doing this,
Automatically Re-apply AutoFilter When Any Change
I'm working on an excel sheet to display technical problems in an office. I want to use a filter in order to not display problems that were resolved (by applying a filter to show only "0" on the "problem duration" column that displays "hours problem was up after solved" and "0" if not solved) as the point is to display only non-resolved issues.
I wanted this filter to be re-applied constantly, meaning once I fill the time and date it's solved and the formula calculates the time it was up, this row will automatically vanish due to the filter applied.
I found this thread on your site Refresh Autofilter Automatically from the resolved problems so we need to remove the filters once in awhile. With this after the filter is removed, you cannot setup a new filter at all (it just doesn't seem to allow you, erasing it after it is being applied). I need to not only be able to setup a new filter, but also that this new filter will be reapplied automatically all the time (temporary disable of a filter is also an option, but I do not know whether it's possible).
I do have Excel 2007 on Vista, but I also need this to work on Excel 2003 with XP.
Automatically Autofilter Upon Data Change
I have a worksheet set up with code to have it Autofilter. My criteria is that if it has 0's in column d then it gets hidden.
This sheet is automatically filled in from information on another sheet.
Is there anyway that when the information is changed that the AutoFilter will automatically refresh itself?
Pivot Refresh After ODBC Query Complete Refresh
I have a query with ODBC connection to a SQL database. This query's parameter is linked to a cell. The resultant data is the source for a pivot table. I want to refresh the pivot table, when the query is run. I've tried using the cell that triggers the query....but the problem is that the query takes about 10 seconds to run. By the time the query returns new data....the pivot has already refreshed. I need it to refresh AFTER the query is complete.
I tried adding a cell that sums up the data from the query...thinking when THAT changes (due to updated data), to trigger the pivot refresh. Problem is that I don't know the trigger for when the sum cell changes (ie....formula change, not typed in.)
Cell Formatting Does Not Refresh Cell Data
When using the format cell function, I have found some cells don't respond to the format until I enter and exit the cell (or edit). Other cells with the same data change when formatting is applied. The cell format is being changed from text to decimcal number. I have a few thousand records and about 30 cells and about half of them change when the format is applied. It would be impossible to manually edit each non-responsive cell to change the format.
Refresh All Pivot Tables On Worksheet When Cell Changes
I have a dashboard that I created that is driven off one source of data. I have several lists and pivots created from that single source. I have two cells that are driven off of lists that then drive the pivot table. When a user selects an item from the drop down list I would like the pivot table to update. I have two tables like this on the main dashboard. Once the user selects the first data point, the table refreshs as does the second drop down list. After they select the second data point, the second privot table will refesh. Manually everything works prefectly, but when I attempt to automate it with VBA, it will only update the initial pivot and the secondary list, but not the second pivot.
I have posted the current code below.
"SelDept1" and "Wave" are the two cells that contain the drop down lists.
Private Sub Worksheet_Change(ByVal Target As Range)
Application. ScreenUpdating = False
Sheet4. Unprotect "lcssdi"
If Target.Address = Range("SelDept1").Address Then
When Query Refresh Its Say "Enable Automatic Refresh"
I've got a spreadsheet with a few queries to extract data into my sheet. Whenever I open the spreadsheet, I get a "query refresh" dialog box, asking if I want to "Enable automatic refresh". I keep clicking on the "Enable" button but I have to keep answering the question for each open. Isn't there a way to set "yean - ok - refresh the data" so that I don't have to keep saying "yes"?
Edit The Cell When It Is In AutoFilter
I want to edit the cell or Specific Range when it is in autofilter mode. Here is the example i want to filter as long as the first column criteria is 1
1 string 1
2 string 2
1 string 1-1
3 string 3
after filtered with criteria = 1
1 string 1
1 string 1-1
now i want to edit both "string 1" and "string 1-1" to "abcd". How am i going to get the the autofilter to return me a range for me to change the cells value.
To Have Cell Equal To Autofilter
I have 2 worksheets, and I was wondering if I can have a cell from Sheet2 equal to the subtotal of the Autofilter criteria of a column in Sheet1.
I do have something working with macros but it takes a long time. This would make things a lot easier.
Use Cell As Autofilter Criteria
I have one master sheet of data, with a large number of fields and data.
I need to turn this master data into individual records, each record exisiting as an individual worksheet - lets call it a 'U'. The U is a template sheet which has calculations and lookups built into it to complete further information. The completed U's are then used by a number of people for different reasons. There are 3 main 'flavours' of these sheets which have slightly different uses.
I've gone from knowing nothing about macros to having learned enough about them in the last week or so to populate each individual sheet with the data, and save the new file in the location I want it to go.
What I want to do now is filter the fields displayed by the individual U sheets, as not every field is applicable to each 'flavour'. I've marked up the rows as to the appropriate flavour - e.g. Row 17 is applicable to 'P' 'F' and 'R' ( Cell which is auto filtered contains PFR), but Row 18 is only 'P' and 'F' (Cell contains PF).
I've gone through the master file and identified each entry as a the appropriate flavour - to summarise what I'd like to do now:
1) Automatically populate the template file with the relevant data. (which my macro will do)
2) Use an autofilter to filter the rows equal to the data in the reference sheet so these are the only ones displayed. Eg. Reference sheet says 'P', so I want to filter the U sheet where autofilter column contains the letter 'P'
3) Rename the file and save as my reference in the location I want it to (which the macro is doing).
Here's what I've got:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Selection.AutoFilter Field:=1, Criteria1:=ActiveCell, Operator:=xlAnd
(where CF3 = the cell in the master data with the flavour in it, "USS iss1" is the template U file, G158 is a spare cell and Autofilter Field 1 contains the row reference which tells me which data applies to which flavours).
I've tried using the macro recorder, which when I paste the value in the autofilter/contains box records it as the value I've just put in rather than a copy of the reference cell. I've tried
and other variations on the same theme, but to no avail - I get "Compile error: Expected:expression".
AutoFilter By Selected Cell Value
My task is to create a sub-routine that filters a column to the currently selected value while capturing the column header for use in a second sub-routine that finds the corresponding column in the second worksheet and filters it using the value from the first worksheet
Autofilter: Multiple Entries In One Cell
I'm making a database for people to easily find their documents at work. In some cells there are multiple entries because the document could be within two catagories, eg. memo and report. Is it possible to somehow list these multiple entries within the cell so that the autofilter will recognise them as seperate entries and find that document whether you filter for memo or report? If not, can you get the filter to search more than one column for the same result and show all entries that are, say, a memo, even if it says so in different columns? I want it to be as simple as possible for the user so that they can select what they want from the drop-down menu and not
Code To Reference A Cell For Autofilter Value
I have a macro which (see below) which will autofilter a spreadsheet to only show cases which are older than a 200 days. I'd like to add the ability for the end user to change the filter criteria as required (say from 200 to 300).
Ideally this would be done by having the macro reference cell G22 which is where I would allow the end user to change the filter criteria.
Autofilter Cell Reference Formula
I've been trying to make a formula using autofilter to search data inbetween a date range which i would like to be specified from 2 ceels on the workbook.
Basically i'm trying to get it to function like access or a search box.
The bit i'm having trouble with is when i record the macro i get to the custom filter option but i am unsure how to link from there to the cells in the spreadsheet.
Next Visible Cell In AutoFilter Range
I am looking for a code which can jump to a next cell from the activecell. I use the code
This would take me to the next cell. However, this is a problem when the filter is on. I am not able to go to the next visible cell. Suppose if the row increase is 1, then cell selection goes to the hidden cell. I need to bypass the hidden cell and go to the next cell. Can anybody provide a code which does it? I tried searching the forum but could not get anything closer to this as I need something which works with offset and not cells.row.visible...etc.,
AutoFilter Using Active Cell As Criteria
My excel file has a column for customer name (there are more columns btw). I have the filter feature on. The customer list is in column F.
I recorded a macro by copying a customer (from the customer column, say from cell F99). Then I click on the filter arrow and select the 'custom' option and in the dialog box that comes up, I paste the copied customer and try to find other records which have the same customer name.
This works great but when I run the macro for some other customer, say from cell F200, the pasting part, pastes the customer that I had selected when recording the macro.
Is there a solution to make this generic so that the macro will work in a manner that I simply highlight the cell of the desired customer and then then run the macro which will give me the result.
Cell Formating Slows Down AutoFilter
I have an AutoFilter list of 14,000 rows by 14 columns, and the cells have some specific formating: fill color, font,
protection status, wrap, etc....
There are an additional 7 columns of formulas to the left of the filtered range.
The strange thing is----
-WITH the formating, trying to Unfilter the list takes 2 min, via a manually activated Data>Filter>ShowAll OR via a macro run of 'ActiveSheet.ShowAllData' .
(In an attempt to optimize speed, the VBA macro sets calculation to manual before the 'ActiveSheet.ShowAllData' and screen updating set to false.)
-WITHOUT the cell formating (eg. by doing Edit>Clear>Formats), the ShowAll takes about 3 sec.
Does anyone have experience or an explanation for this?
Why should the Formating affect Filtering so much?
Options for improving speed of autofilter?
I don't know if, or why it would be a factor, but note that I am using Dynamic Named Range and VBA to expand/contract the formulas
and formating to size of the list/table. Although this is not done during the filtering use.
Here is the dynamic formating code
Application. ScreenUpdating = False
.Calculation = xlManual
I am wondering if some strange 'artifact' of manipulating the formating is becoming a factor?
Use Cell & Wilcard For VBA Autofilter Criteria
Problem: utofilter criteria
This has been my Autofilter criteria to date
Criteria1 = " " (needed for other macros)
Criteria2 = "=2007*" (i.e. starts with JobYear)
This works fine but now I am wanting Criteria2 to be linked to a cell where the user will enter the job year to be filtered.
My guess at solutions would be:
Criteria1 = " "
Criteria2 = Cell*
Criteria2 = Cell & "*"
Unfortunatley I have been unable to find any way of making this happen. Using an Autofilter to link to a cell is standard but making an Autofilter link to a cell with a starts with criteria attached doesn't seem to be doable.
AutoFilter Macro Using Cell Content As Criteria
I was wondering if I could pick up the Field number and the criteria from a cell in excel rather than changing the code in VBA.
Range("A1:K1"). AutoFilter Field:=Range("A1"), Criteria1:=Range("B1")
I was trying this code but I got an error message. Does anyone know if I can pick up these information directly from excel.
Automatically Update A Protected Cell Based On Contents Of An Unprotected Cell
I am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells.
Cell 'A1' starts out blank.
Cell 'B1' has a drop down to select specific items.
Cell 'C1' is the date of the order and is a locked cell.
Cell 'D1' is the date of completion and is a locked cell.
The worksheet is protected to prevent direct changes to cells C1 and D1.
If a user enters any information in A1, then C1 displays the current date.
If a user selects 'Complete' from the drop-down list in B1, then D1 displays the current date. Both of these actions are independant of one another. This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at.
When A Date Entered On A Specific Cell, Automatically Enter A Text In Other Cell
I have 6 Headings in excel named...
"A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1.
There are two projects.
Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E.
My Specification follows...
1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.
Automatically Shade Cell Dependant Upon Text In Aother Cell
Im writing a spreadsheet that will act as a hotel room booking diary. There will be 52 sheets (representing each week of the year) and each sheet will therefore represent one week
Each sheet will look at operate the same. Running horizontally will be the days of the week and running vertically will be each of the bedrooms. There are 22 bedrooms in total and each room will have 12 cells allotted to it for each day - lets call this a 'block' and presume that the first block runs from C10 to c21. The final of these cells C21 will have a validation and the user can select 'PAID' 'TO PAY' or 'INVOICED'
If the user selects 'PAID' in C21 I want cells C10 through to C21 to shade GREEN
If the user selects 'TO PAY' in C21 I want cells C10 through to C21 to shade RED
If the user selects 'INVOICE' in C21 I want cells C10 through to C21 to shade YELLOW
Automatically Clear Cell When Corresponding Cell Is Populated
I have a 7 (columns) by 2 (rows) grid of cells which I need to perform the following,
If cell a1 is populated, then it will clear the contents of a2, or if cell e2 then it will clear the contents of e1. Basically if any cell is populated it will clear the other corresponding column entry.
I came across this code which works well for one cell
Automatically Fill Cell Value Based On Another Cell
I have certain values in cell B4 to B2000. There are 25 unique values in those cells. In cells A4 to A2000 I would like to put a identifier (lets say a letter from A to Y) based on the adjacent B4 to B2000 cell values. I can't use a IF function because I am way over 7 allowed in excel. I could use a vlookup but that seems to slow down the file quite a bit. Is there something else better/faster out there?
Automatically Date Appear In A Particular Cell
I'm looking for a way to achieve the following:
When entering data in any cell in a excel sheet I would like the previous date to automatically appear in particular cell so the cell will show the date I entered something in the cell. I want the date to be a fixed date, I don't want it to change.
On entering something in a different cell in the sheet, the date of the cell remain same.
After saving the sheet, the next time I open it the dates will still have to be the same as they were the first time.