|
http://www.excelpx.com/forum.php?mod=attachment&aid=Mjc1NzY2fGQyOGIzMDNmZDY5NTRiYjkzYTYyYzliMmJhNmJiMjRhfDE3MTQwOTQwMjI%3D&request=yes&_f=.rar
请教高手,EXCEL表分割另存的新文件中图片不能一起移动,用了N种方法都无法解决,请高手赐教,不胜感激!详见附档中的原文件,母文件名:PO list.xls, 其它XLS文件为VBA自动另存的多个新文件,以下内容是VBA代码:
Sub 拆分()
Dim sh As Worksheet, i As Integer, sLocation As String
'Dim objWorkbook As Object
Dim iStartName As Long
Dim Data
Dim 最大行数, j&, TitleRow&, k
Dim Headers&, Splitcol&
Dim dic As Object
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Headers = 1
Data = Range("a1").CurrentRegion '源数据
iStartName = Headers + 1
Splitcol = 4
Set dic = CreateObject("Scripting.Dictionary")
For i = iStartName To UBound(Data)
dic(Data(i, Splitcol)) = dic(Data(i, Splitcol)) + 1
Next
最大行数 = Application.Max(dic.items)
dic.RemoveAll
For i = iStartName To UBound(Data)
If Not dic.exists(Data(i, Splitcol)) Then
ReDim arr(0 To 最大行数 + Headers - 1, 1 To UBound(Data, 2))
For j = 1 To UBound(Data, 2)
For TitleRow = 1 To Headers
arr(TitleRow - 1, j) = Data(TitleRow, j)
Next
arr(Headers, j) = Data(i, j)
Next
dic(Data(i, Splitcol)) = Array(1, arr)
Else
'Stop
arr = dic(Data(i, Splitcol))(1)
k = dic(Data(i, Splitcol))(0) + 1
For j = 1 To UBound(Data, 2)
arr(k + Headers - 1, j) = Data(i, j)
Next
dic(Data(i, Splitcol)) = Array(k, arr)
End If
Next
k = dic.keys
Dim filename$
For i = 0 To dic.Count - 1
With Workbooks.Add
With ActiveSheet
.Cells.NumberFormatLocal = "@"
.[A1].Resize(最大行数 + Headers - 1, UBound(Data, 2)) = dic(k(i))(1)
filename = ThisWorkbook.Path & "\" & .[D2] & "-" & ThisWorkbook.Name
End With
.SaveAs filename:=filename, FileFormat:=xlOpenXMLWorkbookMacroEnabled
', FileFormat:=xlWorkbookNormal
End With
ActiveWorkbook.Close True
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
本帖最后由 zjdh 于 2013-6-1 18:55 编辑
Sub 拆分()
Dim sh As Worksheet, i As Integer, sLocation As String
Dim iStartName As Long
Dim Data
Dim 最大行数, j&, TitleRow&, k
Dim Headers&, Splitcol&
Dim dic As Object
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Headers = 1
Data = Range("a1").CurrentRegion
iStartName = Headers + 1
Splitcol = 4
Set dic = CreateObject("Scripting.Dictionary")
For i = iStartName To UBound(Data)
dic(Data(i, Splitcol)) = dic(Data(i, Splitcol)) + 1
Next
最大行数 = Application.Max(dic.items)
dic.RemoveAll
For i = iStartName To UBound(Data)
If Not dic.exists(Data(i, Splitcol)) Then
ReDim arr(0 To 最大行数 + Headers - 1, 1 To UBound(Data, 2) + 1)
For j = 1 To UBound(Data, 2)
For TitleRow = 1 To Headers
arr(TitleRow - 1, j) = Data(TitleRow, j)
Next
arr(Headers, j) = Data(i, j)
Next
arr(Headers, j) = i
dic(Data(i, Splitcol)) = Array(1, arr)
Else
arr = dic(Data(i, Splitcol))(1)
k = dic(Data(i, Splitcol))(0) + 1
For j = 1 To UBound(Data, 2)
arr(k + Headers - 1, j) = Data(i, j)
Next
arr(k + Headers - 1, j) = i
dic(Data(i, Splitcol)) = Array(k, arr)
End If
Next
k = dic.keys
Dim filename$
For i = 0 To dic.Count - 1
With Workbooks.Add
With ActiveSheet
.CELLS.NumberFormatLocal = "@"
.Columns("E:E").ColumnWidth = 16.5
.Rows("2:" & 最大行数 + 1).RowHeight = 60
BRR = dic(k(i))(1)
.[A1].Resize(最大行数 + Headers, UBound(Data, 2)) = BRR
For j = 1 To UBound(BRR)
If BRR(j, 9) = "" Then Exit For
ThisWorkbook.Sheets(1).CELLS(BRR(j, 9), 5).COPY .CELLS(j + 1, 5)
Next
.Columns("A:D").Columns.AutoFit
.Columns("F:H").Columns.AutoFit
.Range("A1").CurrentRegion.Borders.LineStyle = 1
filename = ThisWorkbook.Path & "\" & .[D2] & "-" & ThisWorkbook.Name
End With
.SaveAs filename:=filename
End With
ActiveWorkbook.Close True
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
|
|