data filter

  • Thread starter Thread starter Pam M
  • Start date Start date
P

Pam M

I have a workbook that has 3 worksheets in it. sheet one has an inflation
index. Sheet 2 is data entry for quotes. Sheet 3 is a duplicate of sheet 2
with the quote price inflated if a cell on Sheet 2 indicates to do so.

Sheet 2 and 3 can be autofiltered to view certain locations. If you are
going to auto filter sheet 2 to enter quotes only for a certain location(s),
then you will want to see only those same locations on sheet 3 inflated. Is
there any way to program the autofilter arrows so that if you filter sheet 2,
it performs the same filter on sheet 3 so that the user doesn't have to
repeat the steps?

Sheet 3 is all formulas, so there is no user intervention to sheet 3 at all
other than the fact that they have to re-filter.
 
Hello Pam,

If you right click on the Sheet3 name and select View code and copy the
following code into the VBA editor then each time you activate sheet3 it
should set the filters the same as sheet2. However with a caveat. If you are
using xl2007 you cannot set more than 2 options in a single dropdown filter.
(previous versions you can only select one or with custom you get to pick 2
using between etc.)

Private Sub Worksheet_Activate()
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim i As Long

Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")

'Ensure autofilter is invoked on Sheet3
With ws3
If Not .AutoFilterMode Then
.UsedRange.AutoFilter
End If
End With

With ws2
'Test if Autofilter invoked on sheet2
If .AutoFilterMode Then
'Test if an actual filter is set
If .FilterMode Then
With .AutoFilter
'Test each filter
For i = 1 To .Filters.Count
With .Filters(i)
If .On Then
If .Operator = 0 Then
ws3.AutoFilter.Range _
.AutoFilter _
Field:=i, _
Criteria1:=.Criteria1
Else
ws3.AutoFilter.Range _
.AutoFilter _
Field:=i, _
Criteria1:=.Criteria1, _
Operator:=.Operator, _
Criteria2:=.Criteria2
End If
End If
End With
Next i
End With
Else
If ws3.FilterMode Then
ws3.ShowAllData
End If
End If
Else
If ws3.FilterMode Then
ws3.ShowAllData
End If
End If

End With

End Sub
 
thank you for the response. I am using Excel 2003. When I invoke the macro,
I am getting a runtime error that I "cannot use this command in a protected
sheet" and the debugger is on the Autofilter section for ws3. I do have the
protection set to allow autofilter, so I'm not sure why it won't allow the
command.
 
There are 2 methods of fixing this. One is to include unprotect and protect
code within the code being run. The other method is to use a separate macro
to invoke the protection and set the UserInterfaceOnly parameter. (I don’t
think this can be done in the userinterface mode).

The second method you can delete the macro after setting the protection so
the password is not visible in the code but make sure you keep a copy of the
password somewhere.

Also note that there was a bug in the previous code. You will see a comment
where I added a line of code. It caused a problem if more than one filter was
set then later one of the filters was turned off.

Following code for first method

Private Sub Worksheet_Activate()
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim i As Long

Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")

'Edit the password to your password.
ws3.Unprotect Password:="ossiemac"

'Ensure autofilter is invoked on Sheet3
With ws3
If Not .AutoFilterMode Then
.UsedRange.AutoFilter
End If
'Following line added to code to remove a bug
If .FilterMode Then ShowAllData
End With

With ws2
'Test if Autofilter invoked on sheet2
If .AutoFilterMode Then
'Test if an actual filter is set
If .FilterMode Then
With .AutoFilter
'Test each filter
For i = 1 To .Filters.Count
With .Filters(i)
If .On Then
If .Operator = 0 Then
ws3.AutoFilter.Range _
.AutoFilter _
Field:=i, _
Criteria1:=.Criteria1
Else
ws3.AutoFilter.Range _
.AutoFilter _
Field:=i, _
Criteria1:=.Criteria1, _
Operator:=.Operator, _
Criteria2:=.Criteria2
End If
End If
End With
Next i
End With
Else
If ws3.FilterMode Then
ws3.ShowAllData
End If
End If
Else
If ws3.FilterMode Then
ws3.ShowAllData
End If
End If

End With

ws3.Protect _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False, _
AllowFiltering:=True, _
UserInterfaceOnly:=True, _
Password:="ossiemac"

End Sub



Second Method.
Remove the protect and unprotect lines from the previous code and then run
the following code on its own to protect the worksheet. You can copy it into
the same sheet module as the other code, place the cursor anywhere in the sub
and press F5 to run it from the VBA editor.

Sub ProtectSheet3()
Worksheets("Sheet3").Protect _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False, _
AllowFiltering:=True, _
UserInterfaceOnly:=True, _
Password:="ossiemac"
End Sub
 
I chose method one, and it worked beautifully. I did add a line to reprotect
the sheet after the End With. thank you so much for your help.
 
Back
Top