如何将excel工作薄中的单列数据复制到不同的新工作簿中

汇总报表中单列公司的数据要分发到下属公司,如果每一列都复制到新工作簿另存,工作很大,所以编写了一段VBA来代替重复的手工劳动
Sub add_new()
On Error Resume Next
With Worksheets("Detail line-consolidation") '汇总报表
For i = 3 To 35
Dim fname As String
fname = .Cells(3, i) '取公司名称
Dim mybook As Workbook
Set mybook = Workbooks.Add
mybook.SaveAs "d:\vba\" & fname & ".xls"
col = Split(Cells(3, i).Address, "$")(1) '取列名
.Range("a2:b169").Copy
Windows(fname & ".xls").Activate
Range("a1").PasteSpecial
.Range(col & "2:" & col & "169").Copy
Windows(fname & ".xls").Activate
Range("c1").PasteSpecial
Workbooks(fname & ".xls").Close savechanges:=True
Set mybook = Nothing
Next i
End With
End Sub

Join the Conversation

2 Comments

Leave a comment

Your email address will not be published. Required fields are marked *