获取AutoFilter排序条件并应用于第二张表 [英] Get AutoFilter sort criteria and apply on second sheet

问题描述

我试图看看是否可以通过编程方式捕获AutoFilter排序事件，获取排序条件，然后将相同的排序条件应用于第二个工作表中的自动过滤器。

到目前为止，好像我必须触发Worksheet_Calculate（）事件。这样我做了。那么我必须检查AutoFilter排序条件是否被更改。如果没有，退出子。如果是，请收集标准并通过单独的子文件运行它，它在单独的工作表中对AutoFilter执行完全相同的排序。

一般的想法是，只要这两个AutoFilter中的一个被排序，另一张表中的AutoFilter应该按照完全相同的方式进行排序。

我试图做这样的事情（我必须添加一个Excel公式来实际计算事件触发器）：

  Private Sub Worksheet_Calculate（）
 Dim wbBook as Workbook 
 Dim wsSheet as Worksheet 
 Dim rnData as Range 
 
设置wbBook = ThisWorkbook 
设置wsSheet = wbBook.Worksheets（Sheet1）
 
带有wsSheet 
设置dnData = .UsedRange 
结束
 End Sub 
  

但我似乎无法设法收集标准，我已经尝试了几件事情，并添加了一个手表到dnData甚至不显示任何AutoFilter属性。有人可以在这方面发光吗？

解决方案

这是一种获取 autofilter 标准：

  Sub test（）
 Dim Header As Range 
 Dim sMainCrit As String ，sANDCrit As String，sORCrit As String 
 Set Header = Range（A2：C2）
 With Header.Parent.AutoFilter 
 With .Filters（Header.Column  -  .Range.Column + 1）
如果不是.On然后
 MsgBox（no criteria）
退出Sub 
结束If 
 sMainCrit = .Criteria1 
如果.Operator = xlAnd然后
 sANDCrit = .Criteria2 
 ElseIf .Operator = xlOr然后
 sORCrit = .Criteria2 
如果
结束
结束
 MsgBox（Main criteria：& sMainCrit& Chr（13）&AND Criteria：& sANDCrit& Chr（13）&OR Criteria& sORCrit）
 End Sub 
  

改编自 ozgrid

I'm trying to see if I can programatically trap an AutoFilter sort event, get the sort criteria and then apply that same sort criteria to an AutoFilter in a second worksheet.

So far it seems as though I have to trigger the Worksheet_Calculate() event. And this I've done. Then I have to check if the AutoFilter sort criteria was changed. If it wasn't, exit sub. If it was, collect the criteria and run it through a separate sub, which does the exact same sorting on an AutoFilter in a separate worksheet.

The general idea is that whenever one of these two AutoFilters are sorted, the AutoFilter in the other sheet should be sorted the exact same way.

I've tried to do something like this (I had to add an Excel formula to actually make the calculate event trigger):

Private Sub Worksheet_Calculate()
     Dim wbBook as Workbook
     Dim wsSheet as Worksheet
     Dim rnData as Range

     Set wbBook = ThisWorkbook
     Set wsSheet = wbBook.Worksheets("Sheet1")

     With wsSheet
          Set dnData = .UsedRange
     End With
End Sub

But I can't seem to manage to collect the criteria, I've tried several things and adding a watch to the dnData doesn't even reveal any AutoFilter property. Can someone shed any light on this?

解决方案

Here is a way to get the autofilter criteria:

Sub test()
Dim Header As Range
Dim sMainCrit As String, sANDCrit As String, sORCrit As String
Set Header = Range("A2:C2")
    With Header.Parent.AutoFilter
        With .Filters(Header.Column - .Range.Column + 1)
            If Not .On Then
                MsgBox ("no criteria")
                Exit Sub
            End If
            sMainCrit = .Criteria1
            If .Operator = xlAnd Then
                sANDCrit = .Criteria2
            ElseIf .Operator = xlOr Then
                sORCrit = .Criteria2
            End If
        End With
    End With
    MsgBox ("Main criteria: " & sMainCrit & Chr(13) & "AND Criteria:" & sANDCrit & Chr(13) & "OR Criteria" & sORCrit)
End Sub

Adapted from ozgrid

这篇关于获取AutoFilter排序条件并应用于第二张表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！