|
按照A列 仓库名称来拆分,保留表头,新生成的文件名就是 仓库名称。 我尝试写了些代码,能力有限,希望能指点我。(用数组来做,不用数据库)
请先保存好副本 - Sub test()
- Dim arr, brr(), r, c, x, y, d, dk
- br = Sheets("sheet1").Range("a1:e1")
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets("sheet1").Range("a2:e" & [a65536].End(xlUp).Row)
- For r = LBound(arr) To UBound(arr)
- If Not d.exists(arr(r, 1)) Then
- d(arr(r, 1)) = ""
- End If
- Next
- dk = d.keys
- For x = LBound(dk) To UBound(dk)
- For r = LBound(arr) To UBound(arr)
- If arr(r, 1) = dk(x) Then
- y = y + 1
- ReDim Preserve brr(1 To 5, 1 To y)
- For i = 1 To 5
- brr(i, y) = arr(r, i)
- Next
- End If
- Next
- Set wb = Workbooks.Add
- With wb
- .Sheets(1).Range("a1").Resize(, 5) = br
- .Sheets(1).Range("a2").Resize(y, 5) = Application.Transpose(brr)
- .SaveAs (ThisWorkbook.Path & "" & brr(1, 2) & ".xlsx")
- .Close 0
- End With
- y = 0
- Erase brr
- Next
- set d=nothing
- set arr=nothing
- End Sub
复制代码
|
|