|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
dtxczjzmp 2012-1-26 21:14 关于你在“如何跨工作簿取数?”的帖子
老师:您好!能否帮我取数的目标工作表再加个排序的代码?(最好按升序排列)谢谢!
短信收到:- Sub Macro1()
- Dim MyPath$, MyName$, arr, brr(1 To 60000, 1 To 4), i&, j&, m&
- MyPath = ThisWorkbook.Path & ""
- MyName = Dir(MyPath & "*.xls")
- Application.ScreenUpdating = False
- Do While MyName <> ""
- If MyName <> ThisWorkbook.Name Then
- With GetObject(MyPath & MyName)
- arr = .Sheets(1).[a1].CurrentRegion
- .Close False
- End With
- For j = 4 To UBound(arr, 2)
- If Len(arr(6, j)) = 0 Then arr(6, j) = arr(6, j - 1)
- Next
- For i = 8 To UBound(arr)
- If Len(arr(i, 1)) > 8 Then
- For j = 4 To UBound(arr, 2)
- If Len(arr(i, j)) Then
- m = m + 1
- brr(m, 1) = arr(i, 1)
- brr(m, 2) = arr(i, 2)
- brr(m, 3) = arr(6, j)
- brr(m, 4) = arr(i, j)
- ' Exit For
- End If
- Next
- End If
- Next
- End If
- MyName = Dir
- Loop
- ActiveSheet.UsedRange.Offset(4).ClearContents
- With [a5].Resize(m, 4)
- .Value = brr
- .Sort Key1:=Range("A5"), Order1:=xlAscending
- End With
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|