Filter på 2 sheets (rettelse i VB-kode)
Hej alle,har en excel-fil som har flere sheets, de 3 første sheets ("1","2" og "3") har næsten samme data i, og jeg vil gerne har det samme filter i "2" og "3" som i sheet nummer "1". Altså det kritereier jeg vælger i de forskellige kolloner i autofilter skal automatisk overføres til de to andre sheets og kun dem!!
HAr fundet noget kode på nettet der virker fint,men den får autofilter på alle aktive sheets, og jeg vil kun have dem på de 2, nummer "2" og "3"!
Her er koden, hvordan ændrer jeg det så det kun virker på de to sheetS:
Sub filter_All_Sheets()
Dim objSheet As Worksheet, objMAinSheet As Worksheet
Dim arrAllFilters() As String
Dim byteCountFilter As Byte, i As Byte
Set objMAinSheet = ActiveSheet
' insert all criteria and address
If insertAllFilters(arrAllFilters, byteCountFilter) Then
Application.ScreenUpdating = False
' If is allright, go on
For Each objSheet In ActiveWorkbook.Worksheets
' don't do on same sheet
If objSheet.Name <> objMAinSheet.Name Then
On Error GoTo errhandler
'check Autofilter, if one is off = switch on
objSheet.Select
If Not objSheet.AutoFilterMode Then
' if sheet doesn't contain some data
Range(arrAllFilters(4, 1)).AutoFilter
End If
' here I know that Autofilter is On
' filter some item
For i = 1 To byteCountFilter
' only 1 criteria (without Operator)
If arrAllFilters(2, i) = 0 Then
Range(arrAllFilters(4, i)).AutoFilter _
Field:=Range(arrAllFilters(4, i)).Column, _
Criteria1:=arrAllFilters(1, i)
' with operator
ElseIf arrAllFilters(2, i) <> 0 Then
Range(arrAllFilters(4, i)).AutoFilter _
Field:=Range(arrAllFilters(4, i)).Column, _
Criteria1:=arrAllFilters(1, i), _
Operator:=arrAllFilters(2, i), _
Criteria2:=arrAllFilters(3, i)
End If
Next i
End If
Next objSheet
Else
'While Main Sheet doesn't contain data or Autofilter is off
MsgBox "Main Sheet (Name """ & objMAinSheet.Name & """) doesn't some data or it doesn't use !" _
& vbCrLf & "This code can't go on.", vbCritical, "Missing Autofilter object or filter item "
Set objMAinSheet = Nothing
Set objSheet = Nothing
Application.ScreenUpdating = True
Exit Sub
End If
objMAinSheet.Activate
Set objMAinSheet = Nothing
Set objSheet = Nothing
Application.ScreenUpdating = True
MsgBox "Finished"
Exit Sub
errhandler:
Set objMAinSheet = Nothing
Set objSheet = Nothing
Application.ScreenUpdating = True
'If Err.Number = 1004 Then
'MsgBox "Probable cause of error - sheet dosn't contain some data", vbCritical, "Error Exception on sheet " & ActiveSheet.Name
'Else
'MsgBox "Sorry, run exception"
'End If
End Sub
Function insertAllFilters(arrAllFilters() As String, byteCountFilter As Byte) As Boolean
' go throught all filters and inserting their address and criterial
Dim myFilter As Filter
Dim myFilterRange As Range
Dim boolFilterOn As Boolean
Dim i As Byte, byteColumn As Byte
boolFilterOn = False: i = 0: byteColumn = 0
' If AutoFilter is off - return False
If Not ActiveSheet.AutoFilterMode Then
insertAllFilters = False
Exit Function
End If
' If Autofilter is on & no filter any item = return false
For Each myFilter In ActiveSheet.AutoFilter.Filters
If myFilter.On Then
boolFilterOn = True
Exit For
End If
Next myFilter
' Check Filter
If Not boolFilterOn Then
insertAllFilters = False
Exit Function
End If
On Error GoTo errhandler
' here is all control done
With ActiveSheet.AutoFilter
For Each myFilter In .Filters
byteColumn = byteColumn + 1
If myFilter.On Then
i = i + 1
ReDim Preserve arrAllFilters(1 To 4, 1 To i)
arrAllFilters(1, i) = myFilter.Criteria1
arrAllFilters(2, i) = myFilter.Operator
If myFilter.Operator <> 0 Then
arrAllFilters(3, i) = myFilter.Criteria2
End If
arrAllFilters(4, i) = .Range.Columns(byteColumn).Cells(1).Address
End If
Next myFilter
End With
byteCountFilter = i
insertAllFilters = True
Set myFilter = Nothing
Set myFilterRange = Nothing
Exit Function
errhandler:
insertAllFilters = False
Set myFilter = Nothing
Set myFilterRange = Nothing
End Function
