用于过滤数据并创建新工作表并将数据传输到其中的 VBA 代码

VBA code to Filter data and create a new sheet and transfer data to it

提问by Jay

I'm new to VBA for excel, I'm trying to do a multiple filter with four criteria on a column containing either of the following strings (trsf ,trf, transfer, trnsf) that is 4 criteria, but I was only able to do it for two, I can't seem to do it for 4, I manually created a new sheet called Transfers but I want the code to automatically create the new sheet and name it Transfers. Please help modify: to allow four criteria and create a new sheet and rename it and transfer the filtered data to the new sheet ,and restore the DataSheet Back to its default state before the filter.

我是 excel 的 VBA 新手，我试图在包含以下字符串（trsf、trf、transfer、trnsf）的列上使用四个条件执行多重过滤器，该列包含 4 个条件，但我只能做两个，我似乎不能做 4，我手动创建了一个名为 Transfers 的新工作表，但我希望代码自动创建新工作表并将其命名为 Transfers。请帮忙修改：允许四个条件并创建一个新工作表并重命名并将过滤后的数据传输到新工作表，并将DataSheet恢复到过滤器之前的默认状态。

Sub ActivateJournalsSheet()
Dim wsj As Worksheet
For Each wsj In Worksheets
If wsj.Name <> "DataSheet" Then
wsj.Select
wsj.Application.Run "Transfers"
End If
Next
End Sub
Sub Transfers()
ActiveSheet.Range("$A:$H30").AutoFilter Field:=2, Criteria1:="=*trsf*", Operator:=xlOr, _
Criteria2:=Array( _
trsfs, _
trnsf, _
transfer), _
Operator:=xlFilterValues
Worksheets.Add.Name = "Transfers"
End Sub

Sub CopyPaste()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "DataSheet" Then
ws.Select
ws.Application.Run "MacroCopy"
End If
Next
End Sub

Sub MacroCopy()
Range("A1:H4630").Select
Selection.Copy
Sheets("Transfers").Paste
End Sub

Thanks Dan, i had to delete this because the strings 'trans' and 'trsf' appear as part of other strings not just as the only content of cells.

谢谢丹，我不得不删除它，因为字符串 'trans' 和 'trsf' 作为其他字符串的一部分出现，而不仅仅是单元格的唯一内容。

'make sure that trans or trsf exists in the check range Set TestTRANS = `CheckRng.Find(What:="trans", LookIn:=xlValues, LookAt:=xlWhole) Set TestTRSF = CheckRng.Find(What:="trsf", LookIn:=xlValues, LookAt:=xlWhole) If TestTRANS Is Nothing And TestTRSF Is Nothing Then MsgBox ("Could not find ""trans"" or ""trsf"" in column B, exiting!") Exit Sub End If`

I also added the second criteria as an array but it'd giving a syntax error . .. the code runs fine with the two initial two criteria , but I want to add trfs and trnsf

我还将第二个条件添加为数组，但它会给出语法错误。.. 代码在最初的两个条件下运行良好，但我想添加 trfs 和 trnsf

With DataRng
    .AutoFilter Field:=2, Criteria1:="=*trsf*", Operator:=xlOr, Criteria2:=Array( _trfs, _trnsf), _Operator:=xlFilterValues
End With

采纳答案by Dan Wagner

I think the code below does everything you're looking for:

我认为下面的代码可以完成您正在寻找的一切：

Option Explicit
Sub BringItAllTogether()

Dim DataSheet As Worksheet, TransfersSheet As Worksheet
Dim DataRng As Range, CheckRng As Range, _
    TestTRANS As Range, TestTRSF As Range, _
    CopyRng As Range, PasteRng As Range

'make sure the data sheet exists
If Not DoesSheetExist("DataSheet", ThisWorkbook) Then
    MsgBox ("No sheet named ""DataSheet"" found, exiting!")
    Exit Sub
End If

'assign the data sheet, data range and check range
Set DataSheet = ThisWorkbook.Worksheets("DataSheet")
Set DataRng = DataSheet.Range("$A:$H30")
Set CheckRng = DataSheet.Range("$B:$B30")

'make sure that trans or trsf exists in the check range
Set TestTRANS = CheckRng.Find(What:="trans", LookIn:=xlValues, LookAt:=xlWhole)
Set TestTRSF = CheckRng.Find(What:="trsf", LookIn:=xlValues, LookAt:=xlWhole)
If TestTRANS Is Nothing And TestTRSF Is Nothing Then
    MsgBox ("Could not find ""trans"" or ""trsf"" in column B, exiting!")
    Exit Sub
End If

'apply autofilter and create copy range
With DataRng
    .AutoFilter Field:=2, Criteria1:="=*trsf*", Operator:=xlOr, Criteria2:="=*trans*"
End With
Set CopyRng = DataRng.SpecialCells(xlCellTypeVisible)
DataSheet.AutoFilterMode = False

'make sure a sheet named transfers doesn't already exist, if it does then delete it
If DoesSheetExist("Transfers", ThisWorkbook) Then
    MsgBox ("Whoops, ""Transfers"" sheet already exists. Deleting it!")
    Set TransfersSheet = Worksheets("Transfers")
    TransfersSheet.Delete
End If

'create transfers sheet
Set TransfersSheet = Worksheets.Add
TransfersSheet.Name = "Transfers"

'paste the copied range to the transfers sheet
CopyRng.Copy
TransfersSheet.Range("A1").PasteSpecial Paste:=xlPasteAll

End Sub

Public Function DoesSheetExist(SheetName As String, BookName As Workbook) As Boolean
    Dim obj As Object
    On Error Resume Next
    'if there is an error, sheet doesn't exist
    Set obj = BookName.Worksheets(SheetName)
    If Err = 0 Then
        DoesSheetExist = True
    Else
        DoesSheetExist = False
    End If
    On Error GoTo 0
End Function