We have many speadsheets within our workplace that alot of people have access to.
These spreadsheets have many sheets (tabs) which all have auto filters set.
The problems we have are people are leaving the auto filters on and then when somebody else goes in the spreadsheet they are reporting wrong information because they havent relaised the filter is still on.
I have in place a macro that removes all filters upon opening the spreadsheet put this currently only works on the first (active) sheet.
The code i have at the moment is below:
'put the autofilter back on if it has been removed.
If ActiveSheet.AutoFilterMode = False Then Rows(1).AutoFilter
'error handle the showalldata part
On Error Resume Next
For Each ws In ActiveWorkbook.ShowAllData
On Error GoTo 0
Is there something i can change for it to apply to all sheets.
More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…