Autofilter is turned on in a weekly spreadsheet that I receive. Functionality to move around by arrows or page up/down works until I select something out of an autofilter drop down. Screen locks in place. Can't arrow down or up (but can go left/right). Page up/down is broke also.
If I exit and re-enter it works until I filter. Scroll lock does nothing.
I have a workbook that contains several macros and many formulas. It
works beautifully until I try to print or do a print preview. Then it locks
up excel and I have to restart. Any ideas about what is causing this?
In a cell there would be a formula, an "if" formula and I want to know is there something in Excel that will lock in a true statement. Like lets say in cell "A1" a formula would read =if(b1>1,.001,.002), I want to lock in a number when the statement is true. Cannot be change no more after when it is true.
View 9 Replies View RelatedEverything works in the script. It is a little slow on some of the users pc's.But they discovered a problem when they paste more than a couple values ,the workbook locks up.
1 I like to know how to speed this up?
2 I would like to understand as to why excel locks up.
You can recreate the lock up by picking a name from the drop down list in column A5 then Copy that name and paste in A6 To A10. I'm using Excel 2002.
In my "MAIN" Sheet. Column A starts at 12th row and contains ONLY Strings. I would like to Autofilter Column A for every unique String (criteria) in Column A (from 12th row to 1000th row). Copy the thus filtered (i.e. visible) cells from (ONLY) column C (for every criteria). Paste the copied cells to a new sheet, starting from A4 of the new sheet. Give the name of the Criteria as the name for the new sheet. Come back to "MAIN" sheet and show a pop up "Sorted".
View 2 Replies View RelatedI have a workbook with several sheets, but just on the sheet called “sales” I need a macro to do the following:
If F4 = CST
Then Lock down H4-S4 and AM4-AZ4
But if F4 is changed to something else I need H4-S4 and AM4-AZ4 to be unlocked
Is a password necessary for this? It is ou
I just want it to lock and unlock, not protect and unprotect.
I am importing external data to "Workbook A" from the source workbook, "Workbook B." Workbook A is set up to refresh every 15 minutes. Workbook B is shared. I am recieving an error saying either, the 'file is in use', or 'the file is locked,' depending on the senario.
Scenario 1:
1. I open A and enable data connections.
2. I try to open B, but receive the message: '(File location...) is in use. Try again later.
Scenario 2:
1. I open B.
2. I open A.
3. I go back to B to edit information.
4. When I try saving the information I get the following message: 'File is locked. Try the command again later.'
I need to be able to have A open and periodically refreshing. I also need to allow users unhindered access to B at the same time.
I've been having trouble recently with my workbook file size growing rapidly due to copy and paste commands and such. I found a very nice macro to reduce file size called ExcelDiet located at [url]
Option Explicit
Sub ExcelDiet()
Dim j As Long
Dim k As Long
Dim LastRow As Long
Dim LastCol As Long
Dim ColFormula As Range
Dim RowFormula As Range
Dim ColValue As Range
Dim RowValue As Range
Dim Shp As Shape
Dim ws As Worksheet
Application. ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
This code works fine until I share the workbook. Can anyone take a look at this code and provide some information as to why this might be happening?
[URL]....
I have 4 workbooks, all of them contain data that is managed by other teams at my company. I need to take all of this data, organize, and concatenate it into one sheet for myself, that I will then reference in other workbooks to various OTHER departments in our company that need the data for certain projects. I have the organization laid out in a lot of very complex formulas so that most of this is automated, and not manual.
I need a solution that can pull data from the 4 workbooks without locking them for use, to allow me to keep my document open all the time, and they can update theirs at their leisure. Everything works fine, until I hit the refresh button. Once this is hit, all of the linked documents are locked and cannot be opened, even in "Read Only" mode. Once I close the master document, the files are free to be opened. I know a workaround for now is that i can not "refresh" the document, but rather close and re open it for changes.
Some steps I have already tried :
1. Create an intermediary file that no one uses, and can be locked all day without a problem. This doesn't work for me since the intermediary file needs to be open to refresh, which locks the original source doc, leaving me in the same place as before.
2.Modify the connection string to display "Mode=Read;" instead of "Mode=Share Deny Read"
3.Create a new connection with "Read" only selected in the Advanced Tab
I have 6 working tabs in my workbook (1-10, 2-8, 1-67, 3-16, 2STB, 204th). Each of these sheet has the same number of columns. The first row is a header and the last row in each sheet is used to calculate subtotals and contains no other data. Each row of data is essentially an order that contains an ID number, information on the equipment being ordered, and information on the customer, remarks, and some other tracking information. Each worksheet contains a Column called "Gaining BDE" (Column E). These "Gaining BDEs" are essentially a way of labeling a group of customers. What I need a macro to do is create and maintain a sheet for each of these "Gaining BDEs".
I need excel to look at each of my 6 working tabs and when it sees 1ABCT, for example, copy that entire row of data into a tab called 1ABCT. As I add rows to my 6 working sheets as orders accrue, I would like excel to automatically place a copy of that row in the appropriate sheet. I have a few other sheets in my workbook that I do not want excel to search for data within as they simply serve as references for my VLOOKUP functions. I would also like the cells to be linked so that when I change the Remarks column to reflect "Complete" for example, that change is reflected in these newly created sheets. I have tried working with other peoples copy/paste row VBA code but have not had any luck and I am brand new to VBA so I have a tough time understanding it still.
I'm having some trouble getting my workbook uploaded but a view of what one of my sheets looks like can be seen here: [URL] ........
I've locked certain columns on my worksheet so that users cannot overtype target dates etc. I've password protected the worksheet.
The password protection means that for some reason the users can't use the autofilters that are on the header row.
Can someone help me solve this problem; I still need the locked cells and password protection on the sheet but the success of the sheet depends on users being able to filter for specific rows using the autofilter....
I have a workbook that uses Autofilter.
Column A has data. Column B is hidden, has no data, and is empty.
Colmn C has data, on through the rest of the sheet (to Column AB).
Why column B has no data is unimportant for now. It, however, must maintain its existance and cannot be deleted.
When I select the autofilter sort button on column A, it selects only that column, and not the rest of the sheet. Data is subsequently scrambled.
When I unhide the empty column B and select the sort button on column B, it selects the entire sheet. Data remains intact.
When I select the sort button on column C, it selects the rest of the sheet, but not column A (with data), or B (with no data). Data is subsequently scrambled.
i am uable to use autolfilter when protecting sheet via vbacode even though i have checked the autofilter box to be able to use it whilst protected, why does it work when i manually do protection but does not work via vba?
View 9 Replies View RelatedTrying to filter list and copy filtered data to another sheet in the same workbook.
I'm receiving this error:
Run-time error '438'
Object doesn't support this property or method
Worksheets("Data"). CurrentRegion.Copy _
Worksheets(" Totals"). Range("A1")
Sub GetTotals() ..............
I have 2 sheets. The first sheet contains rows of data that I wish to search (Query Results) through and find matching data. The second sheet is the source of the data I want to find (Notifications). I want to take the number located in column A on sheet 2 and then use it to auto filter the rows on sheet 1. With the range that is left visible on sheet 1 I want to select the first row (can offset(1,0) to remove the headers, and paste the selected row back in to Sheet 2 ontop of the row where I sourced the original number to filter by.
Once the item is pasted I then want to pick up the next number from Sheet2 to repeat the process until all numbers have been processed in Sheet2. I have been going round the block on this for the past few days!
Sheets("Query Results").Select
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Selection.AutoFilter Field:=1, Criteria1:=vFroID
Selection.AutoFilter Field:=15, Criteria1:=vReqData
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
On Error Goto 0
End With
vDestRow = "A" & vLoopCount
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Proposed Notifications").Range(vDestRow)
End If
vLoopCount = vLoopCount + 1
How do I determine if AutoFilter is turned on when I open a sheet? I tried FilterMode with no luck.
HTML Code:Â
Sub macro2()
Dim t As Boolean
s = ActiveWorkbook.Name
t = Worksheets("SO_PO_14").FilterMode
End Sub
The FilterMode call always return False,
When I either manually check the box for allowing sorting and autofiltering, or I do it through vba with:
Code:
With Sheets("Inventory")
.Protect Password:="###", AllowSorting:=True, AllowFiltering:=True, AllowUserInterface:=True
.EnableAutoFilter = True
End With
I still am unable to use the icons. The icons are clickable to filter or sort, but every time they say that the cells I am selecting are protected. I want them to be protected from editing the data, but want to allow them to be sorted or filtered.
I have a sheet that I need to routinely filter for a specific code then paste it into a different sheet in the same workbook. I would love to set up a simple macro that would do this for me, but I can't seem to figure it out.
In the results I would like the header row if at all possible, but I can always just make it part of the macro.
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).
I have autofilter enabled on the header row across the top of my worksheet. Is there a quick line of VBA I can use to toggle EACH one to (ALL). This would act as a "Reset" in my spreadsheet and display all content. I had a solution that worked, until I made the workbook shared / password protected.
View 3 Replies View RelatedIf I protect a sheet but tick the boxes to allow sorting and autofilter I still get an erro when I try to use the sort buttons on the autofilter.
It allows me to use the filters but as soon as I sort I get told to unprotect the cells im trying to sort.
Is there a way around this where I can keep formulas from being touched but allow sorting of data through autofilter?
I have a protected worksheet with some macros running on it, and I have been requested to make the workbook shared. Trying to implement this, I keep getting the standard "1004 You cannot use this command on a protected sheet" error when applying an autofilter.
Here is what is making this problem a little tricky:
- As the workbook is shared, I cannot do Protect UserInterfaceOnly because you can't change the protection settings on a shared workbook without unsharing it. Needless to say, I cannot unshare the workbook.
- When I protect the sheet, I do allow the user to use autofilter. Consequently, Sheet.Protection.AllowFiltering and Sheet.EnableAutofilter both are on. Still, I keep getting the error.
The failing line of code is:
shData. Range("_filterDataBase").AutoFilter lngField, strArg
where shData is the codename of the sheet, lngField is the number of the field and strArg is the filtering criteria.
My guess is, that the error comes from the fact that (for some silly reason) in VBA the .AutoFilter method is used to create a new filter, not only to use an existing one. The members of Sheet.AutoFilter.Filters collection are read-only, and cannot be used to modify the existing filter.
Im sure this is a very common problem. I tried searching for it but I havent found anything that solves this for me. Here is the code Im using:
View 3 Replies View RelatedI have created a workbook that imports data from another workbook which is used frequently on a network drive. After I import the data to my new workbook, it locks the original workbook for editing. Is there a property that will allow me to disable this 'locked for editing' read only mode or any other way to get around this?
View 6 Replies View RelatedI'm trying to write a program for work. One sheet (whereiseverything.xls) will have a list of parts column E and where it is in the process column (k). Column E of this sheet will have multiple part numbers. (Sometimes duplicated.) I would like to figure out how to write a code to Auto filter (whereiseverything.xls) sheet and copy only one P/N from (whereiseverything.xls) column E onto another Workbook Worksheet (Commit status.xls) column. After that the sheet must copy all of the locations of that P/N from whereiseverything.xls column K into and under the P/N of the Commitstatus.xls.
It will continue to autofilter and copy from where is everything, the "one" p/n and all of its locations into another empty column of Commitstatus.xls until it no longer has part numbers to autofilter on whereiseverything. I am extremely green on VBA programming but here is my first attempt.
Windows("Where is everything commits .xls").Activate
Windows("whereiseverything[1].xxx").Activate
Columns("D:D").Select
Selection.AutoFilter
Windows("Where is everything commits .xls").Activate
Windows("whereiseverything[1].xxx").Activate
Selection.AutoFilter Field:=1, Criteria1:="7516113-905" \<--this Is one p/n
Columns("J:J").Select
Selection.Copy....................................
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 can set the autofilter, change it, remove all using Macros. What I need to know how to do is reset a filtered column to ALL while leaving all other filters on.
View 2 Replies View RelatedI have a sheet that is password protected. I have this code attached to a command button. It will unlock the worksheet, autofilter it, print it, unfilter it, and password protect the sheet again. However it is protecting it without a password. I need to have it protected with the password so that someone will not be able to just go to tools to unprotect the sheet.
Private Sub CommandButton1_Click()
ActiveSheet.Unprotect "rainforest"
Columns("O:O").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1
Selection.AutoFilter
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub
auto filter options in excel. For some reason it is only sorting up to row 140.
View 14 Replies View RelatedSay you have a long list of data, and you go to Data menu --> Filter --> AutoFilter
And then you want to use the Custom AutoFilter. Here's a screenshot:
[url]
Is there any way to do MORE than two autofilters?