Question

excel批印每页时如何自动小计

excel打印时每页如何自动小计,最后总计

Answer 1

Sub 分页小计()
If ActiveSheet.ProtectContents Then MsgBox "工作表已保护,本程序拒绝执行！", 64, "提示": Exit Sub
Dim columm As Integer, colunn As Integer, A_row As Long, T, I As Integer, J As Byte
columm = Application.InputBox("请输入需要汇总之首列数，" & Chr(10) & "将从该列开始产生小计及累计和。" & Chr(10) & "如果你只需要汇总一列，请在汇总末列处输入同样数字即可。", "汇总首列", 3, , , , , 1)
colunn = Application.InputBox("请输入需要汇总之末列数，" & Chr(10) & "将从首列至此列之间的单元格产生小计及累计和。", "汇总末列", 5, , , , , 1)
If columm = False Or colunn = False Then GoTo err
If 3 = False Or columm = False Or 5 = False Or colunn = False Then GoTo err
T = 2
If ExecuteExcel4Macro("Get.Document(50)") > 1 Then
I = Application.ExecuteExcel4Macro("INDEX(GET.DOCUMENT(64),1)") - 5
Else
MsgBox "对不起，您的文件不足一页，此功能无效。", vbOKOnly + 64, "提示"
Exit Sub
End If
Application.ScreenUpdating = False
X = I + 2
A_row = Range("A65536").End(xlUp).Row
Do While A_row >= X
Rows(X).Insert Shift:=xlDown
Rows(X).Insert Shift:=xlDown
Cells(X, 1) = "本页小计"
Cells(X + 1, 1) = "累 计"
For J = columm To colunn
Cells(X, J).Formula = "=SUM(R[-" + CStr(I) + "]C:R[-1]C)"
Cells(X + 1, J) = "=SUM(R[-" + CStr(I + 2) + "]C:R[-2]C)"
Next J
ActiveWindow.SelectedSheets.HPageBreaks.Add before:=Rows(X + 2)
X = (I + 2) * T
T = T + 1
A_row = A_row + 2
Loop
A_row = Range("A65536").End(xlUp).Row
Range("iv65536").FormulaArray = "=MAX((R[-65535]C[-255]:R[-1]C[-255]=""累 计"")*ROW(R[-65535]:R[-1]))"
For J = columm To colunn
Cells(A_row + 1, J).FormulaR1C1 = "=SUM(R[-" + CStr(A_row - Range("iv65536").Value) + "]C:R[-1]C)"
Cells(A_row + 2, J).FormulaR1C1 = "=SUM(R[-" + CStr(A_row - Range("iv65536") + 2) + "]C:R[-2]C)"
Next J
Cells(A_row + 1, 1) = "本页小计"
Cells(A_row + 2, 1) = "累 计"
Range("IV65536").Delete
Range(Cells(A_row + 1, 1), Cells(A_row + 2, ActiveSheet.UsedRange.Columns.Count)).Borders.LineStyle = xlContinuous
Range([b2], Cells(2, ActiveSheet.UsedRange.Columns.Count)).EntireColumn.AutoFit
Columns("A:A").HorizontalAlignment = xlLeft
Cells(1, 1).Select
Application.ScreenUpdating = True
Exit Sub
err:
MsgBox "对不起,您未指定求和列，程序即将退出。", vbOKOnly + 64, "提示"
End Sub

Answer 2

分页预览，在合适的单元格用sum小计。

Answer 3

http://v.youku.com/v_show/id_co00XMjYzNzc1NDg=.html
分类汇总 的视频介绍

Answer 4

1 文件
2 页面设置
3 页眉页脚
4 自定义页脚
5 选第二个

Answer 5

用分类汇总