是的,可以做到 . 将此代码添加到工作簿中的模块 . 您可以直接从调试器运行它,或转到宏并为其分配一个热键,或者您可以在工作表上创建一个按钮并将此代码放在按钮单击事件中 . 可能有更有效的方法来做到这一点,但除非你有大量数据,否则无关紧要 . 我已经明确了所以你可以看到最新情况 . 我使用了工作表名称“数据”和“查看”,您必须更改代码中的名称以匹配您的工作簿 .
编辑:我还假设您的屏幕截图中左上角的单元格是A1,否则您将不得不调整代码 .
Sub GetDepartments()
Dim LastRow As Long
Dim MyRange As Range
Dim SourceRow As Long
Dim DeptValue As String
Dim OutputRow As Long
Dim ColCounter As Long
'get size of current view sheet and clear
LastRow = ThisWorkbook.Worksheets("View").Cells(ThisWorkbook.Worksheets("View").Rows.Count, "A").End(xlUp).Row
If LastRow > 7 Then
Set MyRange = ThisWorkbook.Worksheets("View").Range("A7:C" & LastRow)
MyRange.ClearContents
End If
'get size of data sheet
LastRow = ThisWorkbook.Worksheets("Data").Cells(ThisWorkbook.Worksheets("Data").Rows.Count, "A").End(xlUp).Row
'get value to match
DeptValue = ThisWorkbook.Worksheets("View").Cells(3, 2).Value
'track outputrow
OutputRow = 7
'loop through all rows in data
For SourceRow = 2 To LastRow
'if match found
If ThisWorkbook.Worksheets("Data").Cells(SourceRow, 2).Value = DeptValue Then
'copy 3 cols
For ColCounter = 1 To 3
ThisWorkbook.Worksheets("View").Cells(OutputRow, ColCounter).Value = _
ThisWorkbook.Worksheets("Data").Cells(SourceRow, ColCounter).Value
Next ColCounter
'increment outputrow
OutputRow = OutputRow + 1
End If
Next SourceRow
End Sub