Excel VBA常见问题:汇总表拆分程序怎么写?

2015-04-13 18:34 阅读 109 次 评论关闭

Excel VBA常见问题:要将总表里的数据按工作单位字段拆分成数个分表(每个单位一张表格,标签名字为工作单位)这个程序怎么编写,请高手指点。如果记录增多或字段增多(但拆分字段不增)这个程序又应该怎样改写,请高手稍微讲解一下,应为我不是为这一个表,还想用到别的工作表中,谢谢!

解答:Sub Add_data(sht_Name)   '找出要取资料的区域
Dim i As Integer, j As Integer, row_d As Integer
Dim First_row As Integer, Last_row As Integer
On Error Resume Next
With Sheets("总表")
i = 1
Do Until .Cells(i, 3).value = sht_Name
i = i + 1
Loop
First_row = i

j = First_row
Do Until .Cells(j, 3) <> sht_Name
j = j + 1
Loop
Last_row = j - 1
End With
Sheets("总表").Range(Cells(First_row, 1), Cells(Last_row, 12)).Select
Selection.Copy
Sheets(sht_Name).Select
Range("A2").Select
ActiveSheet.Paste
With ActiveSheet
row_d = .Range("A2").End(xlDown).Row + 1
Range("B" & row_d).value = "合计"
For i = 5 To 11
Cells(row_d, i).value = Application.WorksheetFunction.Sum(Range(Cells(2, i), Cells(row_d - 1, i)))
Next i
End With

Sheets("总表").Activate
Range("A2").Select

End Sub

版权声明:本文著作权归原作者所有,欢迎分享本文,谢谢支持!
转载请注明:Excel VBA常见问题:汇总表拆分程序怎么写? | 猎微网

评论已关闭!