|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub test()
- Dim i, j, r, k, m, path, name, arr, brr, crr()
- Dim wk As Workbook
- path = ThisWorkbook.path
- With Sheets("sheet3")
- name = .Range("C3")
- End With
- With Sheets("sheet2")
- arr = .UsedRange
- End With
- With Sheets("sheet1")
- r = .Cells(.Rows.Count, 2).End(xlUp).Row
- brr = .Range("B2:B" & r)
- End With
- m = 1
- ReDim Preserve crr(1 To UBound(arr, 2), 1 To m)
- For k = 1 To UBound(arr, 2)
- crr(k, 1) = arr(1, k)
- Next
- For i = 1 To UBound(brr)
- For j = 2 To UBound(arr)
- If brr(i, 1) = arr(j, 2) Then
- m = m + 1
- ReDim Preserve crr(1 To UBound(arr, 2), 1 To m)
- For k = 1 To UBound(arr, 2)
- crr(k, m) = arr(j, k)
- Next
- crr(1, m) = m - 1
- Exit For
- End If
- Next
- Next
- Set wk = Workbooks.Add '新建工作薄
- Application.DisplayAlerts = False
- With Sheets("Sheet1")
- .[A1].Resize(UBound(crr, 2), UBound(crr)) = Application.Transpose(crr) '提取的数值存入工作薄的Sheet1中
- End With
- wk.SaveAs Filename:=ThisWorkbook.path & "/" & name & ".xlsx" '另存工作薄
- wk.Close False
- End Sub
复制代码
|
|