I am looking for a way to have some VBA code running in the background of a worksheet.
I would like the autofilter criteria to be based on a cell reference which can change based on what this cell value is?
I am looking for a way to have some VBA code running in the background of a worksheet.
I would like the autofilter criteria to be based on a cell reference which can change based on what this cell value is?
I can select the top cell in column "F" after filtering by multiple columns using VBA and arrays, but now want to I want to use the top cell in column "F" to search for all other equipment that uses this item.
E.g. remove filter, and reapply autofilter to column "F" based on selected cell as per below VBA
Note: Row 1 contains command buttons and row 2 Headers.
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...
I'm trying to create an autofilter that can filter each category that gets listed in a specific cell.
I have some code from another sheet which I'm trying to modify to accept more than 1 filter criteria.
[Code] .......
Where W2 lists something like:
"Blasting, Flotation, Drilling" and I want it to filter all three categories in the location J24 to the last row with data in it.
I believe I need to apply "*" in locations but I'm not sure where?
I am trying to get autofilter to work based on cell contents.
Code:
ActiveSheet.Range("$C$1:$C$18371").AutoFilter Field:=1, Criteria1:=">=1.5" _
, Operator:=xlAnd, Criteria2:="
I have a table of data which I would like to filter based on a manual cell input instead of the autofilter option.
The table has tickers in column B and has information from column B to U. I would like to filter the data based on a manual ticker that I enter.
I am looking for a way to autofilter a list in "Sheet2" based on the value in Cell A2 of the ActiveSheet. I have about 20 tabs in a workbook that need to be populated with a monthly sheet that is pasted into "Sheet2" every month.
View 1 Replies View RelatedI've created drop-down lists in cells B3 and B4 (this is in worksheet 1), based on a column in Worksheet 2. I'd like Worksheet 2 to autofilter the data based on the drop-downs in B3 or B4, or both together.
View 2 Replies View RelatedI 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,
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.
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.
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?
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?
I'm trying to use " autofilter". I put the code as
Worksheet_Change(ByVal Target As Excel. Range)
so it'll update by itself. My criteria are on row 5 and all my data is below row 7. Row 7 contains the headers.
This all works fine for text-fields. My question is if someone can get it working with numbered fields?
I am looking to adapt a piece of code (originally created by Ger Plante) so that it autofilters multiple columns of a table. I have three data validated lists that need to search 3 different columns in the table and filter accordingly, but also show all if no hits are made (hence why Ger Plante's code) was perfect in most respects. I would ideally like to keep the code as a Worksheet_Change event, but can deal with it being run as a normal Macro via a button if this is necessary.
[URL]....
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1")) Is Nothing Then
Range("A5:C5").AutoFilter Field:=1, Criteria1:=IIf(Trim(Range("B1").Text) = "", "<>", "=") & Range("B1").Text
End If
End Sub
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.
For example:
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.
I am trying to enter a value in a cell which is dependent on a corresponding value in a table. I have a table A which has a cab type and a corresponding cab number.
What I want to achieve is that when I enter the cab number in another table say in cell (D3), the cab type is automatically entered in cell (C3).
Table A
Cab Type
Cab No
Indigo
100
Indigo
758
Innova
1544
Indica
1675
I am trying to enter a value in a cell which is dependent on a corresponding value in a table. I have a table A which has a cab type and a corresponding cab number. What I want to achieve is that when I enter the cab number in another table say in cell (D3), the cab type is automatically entered in cell (C3).
Table A
Cab Type
Cab No
[Code].....
I am trying to create a tracking spreadsheet in which modules need to move through certain phases. I have tabs for all appropriate phases. I would like to be able to select from a pull-down the current phase and have the spreadsheet automatically populate the appropriate tab. Ideally, I would like to be able to edit the process in any of the phases and have it backfill as well (ie if the status changes while in phase 3, I don't have to go back to phase 1 to change it, I can edit it in the phase 3 tab and phase 1 will automatically update).
Additionally, I would like it to calculate this constantly not just when the spreadsheet opens.
I have achieved this partially with formulas but I am finding that it is extremely slow and also doens't always update correctly. I also can't get the circular referencing to work so I don't have full capability using the formulas.
I have a macro that copies data from 6 different worksheets into one summary worksheet - based on certain criteria - I need a way to note on each row of the summary sheet which worksheet it was copied from. Each worksheet is specific to a salesperson, so ideally I would like that salespersons last name to populate in column on the summary page for each line of theirs that is copied over....here is my current macro I use to copy the data: (this scrpit is repeated for each sales persons sheet - "Blankenship", "Dew", etc...)
View 5 Replies View RelatedIn my sheet called summary I have the names of the rest of the tabs in the book in cells B5 to B34. We want to be able to change the names of the tabs by changing their respective cell on the summary tab. So “sheet1” corresponds to B5, “sheet2” to B6, “sheet3” to B7, etc.. So if I change the name in B5 to say “APPLE” I want the tab for sheet1 to change to APPLE. When I change B10 from “sheet6” to “Lemon” I want sheet6 to be titled “Lemon”.
View 5 Replies View RelatedI have a simple projectlist on sheet1 formatted like this:
Column A=Projectname, B=Description, C=Owner, D=Deadline and E=Status.
On sheet2 I have the same columnheaders.
What I would like to do, is that if Status changes to "Done",
* the entire row should be copied to a free row on sheet2, or just inserted on sheet2.row2 without overwriting anything
* the now empty row on sheet1 should be deleted (so there're no empty rows)
* the data on sheet2, should be sorted with D as first key and C as second.
Then, if Status on sheet2 changes to "Reopened", the same procedure should happen but from sheet2 to sheet1.
My first choice was to create a checkbox on each row in the Status column,but I noticed it didn't seem to follow with cut/paste even though I changed the property Locked to false. Anyone knows if it's possible to change this behaviour?
I only know small bits of VBA but my idea is this: ...
I have created a detention tracking sheet for my school. I need an email to be automatically sent to the teacher when a student does not show up for detention.
Here is what I have currently coded, but the macro is not running, and the email is not being sent. I do not understand why.
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes" ....
I have recently used the following code to name a sheet from a cell within that sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("D1") Then ActiveSheet.Name = Target
End Sub
What do I need to add to this bit of code to allow me to name the sheet from another sheet's cell i.e Sheet1 gets named from say Sheet2 Cell A2
I currently use spreadsheets to report on backup processes.
I am trying to speed up this process and one thing that woudl help me is a function that will automatically fill the row with a colour depending on the choice in the dropdown box, so if you choose 'successful' from the dropdown list the row colour would fill to green and if you select 'failed', the row would change to red and a choice of 'completed/errors' would result in orange.
This process is currently done manually and often results in errors like a succesful result with a red filling.
Is there anyway to automate this process? i was trying to whip up a macro, but have been unsuccessful so far.
I have a 'Summary' worksheet which is shown as:
August
September
October
Example 1
1
4
5
The table, which works correctly, looks in another worksheet 'Report' for 'Example 1' within the date range of August. This uses COUNTIFS.
Ideally, what I'm wanting to do is have an auto-filter on each of the cells - 'Example 1', 'Example 2', so if a user wants to see what rows on the 'Report' sheet, falls into this criteria, it auto filters the other worksheet, and takes them too it.
The action would be - Click on the cell of 'Example 1', the macro auto-filters based on the cells value in column F of the 'Report' sheet, and takes the user there. The macro would have to take into account the date filter as well, which uses cells A1,B1 for August, and C1,D1, for September.
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?
View 3 Replies View RelatedTrying to have A1 in Sheet1 having three values 1,2,3 if A1 = 1 hide rows from 1:5 and 10:1500 in three Sheets2,3,4
View 8 Replies View RelatedI would like to automatically color the fill of a cell based on the cell contents.
For example, if the cell content is "1" I would like the color of the cell to be cyan and if the cell content is "2" I would like the color the cell to be red.
I've tinkered with several different formulas but I just can't get anything to work.'