我正在尝试筛选位于不同工作簿上的表。
场景:我有两本工作簿。WB1有几个下拉菜单来过滤WB2上的数据。
我想根据WB1上的多个标准过滤和复制WB2中的数据,并将其粘贴到WB1上现有的选项卡以及上
我已经为此工作了大约8或9个小时,但没有成功。
到目前为止,这就是我所拥有的:
Sub filter_by_cell_value()
Dim WB1 As Workbook
Dim WB2 As Workbook
Set WB1 = ActiveWorkbook
Workboooks.Open Filename = "C:UsersnameDocumentsjblExtract.xlsb"
'Capture new workbook
Set WB2 = ActiveWorkbook
With ActiveWorkbook.Sheets("AU06").AutoFilterMode = False
'Workbook("Extract.xlsb").Sheets ("AU06")
.AutoFilter Field = 2, Criteria1:="=" & ThisWorkbook.Sheets("Appendix 2").Cells(2, 9).Value
.SpecialCells(xlCellTypeVisible).Copy
End With
WB1.Activate
ThisWorkbook.Sheets("AU06").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Range("A1").CurrentRegion.Columns.AutoFit
End Sub
在这一点上的任何帮助都将不胜感激。
在没有看过工作簿的情况下,我可以猜测代码可能看起来像这样:
Sub filter_by_cell_value()
Dim WS1 As Worksheet, WS2 As Worksheet
Set WS1 = ThisWorkbook.Sheets("AU06")
Set WS2 = Workbooks.Open("C:UsersnameDocumentsjblExtract.xlsb").Sheets("AU06")
With WS2
.AutoFilterMode = False
With .Range("A1").CurrentRegion ' your own range
.AutoFilter Field:=2, Criteria1:=WS1.Parent.Sheets("Appendix 2").Cells(2, 9).Text
.SpecialCells(xlCellTypeVisible).Copy
End With
End With
With WS1.Range("A1")
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.CurrentRegion.Columns.AutoFit
End With
End Sub