|
总表有10000行数据,现在为了方便打印,要把它拆分成每50行为一个工作表(不是工作薄哦,当然如果实在不行,工作薄也行)需带表头,并对拆分后的表进行汇总求和。表格格式如图
因为数据较多,附件里仅截取了部分方便上传。想要的效果:
将总数据按排列次序拆分成每50行一个工作表,需要有表头,同时对拆分后的表格汇总求和。而且,拆分生成的表格 需要保持格式,主要是时间和数值不能乱。
之前有位大侠帮忙弄成拆分成工作薄,但是在求和汇总那里,所得的数据永远都是母表里的1-50行的和。。。其代码如下:
Sub aa()
Dim newbook As Workbook
a = ThisWorkbook.Name
b = ActiveSheet.Name
Application.ScreenUpdating = False
For n = 0 To Range("a65536").End(xlUp).Row \ 49
Set newbook = Workbooks.Add
With newbook
newbook.Activate
Workbooks(a).Sheets(b).Rows("1:1").Copy newbook.Sheets(1).Cells(1, 1)
Workbooks(a).Sheets(b).Rows((49 * n + 2) & ":" & (49 * n + 50)).Copy newbook.Sheets(1).Cells(2, 1)
.Sheets(1).Cells(51, 1) = "合计"
.Sheets(1).Cells(51, 2) = Application.WorksheetFunction.Sum(Range("B2:B50"))
.Sheets(1).Cells(51, 3) = Application.WorksheetFunction.Sum(Range("C2:C50"))
.Sheets(1).Cells(51, 5) = Application.WorksheetFunction.Sum(Range("E2:D50"))
.SaveAs Filename:=ThisWorkbook.Path & "\" & Replace(a, ".xls", "") & n & ".xls"
.Close
End With
Next n
Application.ScreenUpdating = True
End Sub
求大神解答!!非常感谢!!
|
|