VBA 生成新的表格

来源:互联网 发布:淘宝网安全地垫 编辑:程序博客网 时间:2024/07/24 01:17
Sub 生成对账单()  Dim i As Integer, j As Integer ' 定义循环变量  Dim endRow  As Integer  Dim count  As Integer  Sheets(1).Select '激活1工作表  Range("B1").End(xlDown).Select  endRow = Selection.Row  endRow = ActiveSheet.UsedRange.Rows.count '当前工作表中已使用的行数  For i = 1 To endRow    If Cells(i, 1) = "应收账款" Then      count = ActiveWorkbook.Sheets.count      Sheets(2).Select      Sheets(2).Copy After:=Sheets(count)      If i < 10 Then        Sheets(count + 1).Cells(3, 4) = "00" + CStr(i) '编号      ElseIf i < 100 Then        Sheets(count + 1).Cells(3, 4) = "0" + CStr(i) '编号      Else        Sheets(count + 1).Cells(3, 4) = CStr(i)     '编号      End If      Sheets(count + 1).Cells(3, 1) = Sheets(1).Cells(i, 2) '单位      Sheets(count + 1).Cells(16, 2) = Sheets(1).Cells(i, 4) '应收帐款      Sheets(count + 1).Cells(15, 2) = Sheets(1).Cells(i, 5) '分期收款发出商品      Sheets(count + 1).Cells(17, 2) = Sheets(1).Cells(i, 5) + Sheets(1).Cells(i, 4) '合计金额      Sheets(count + 1).Select      Sheets(count + 1).Name = CStr(i)              Sheets(1).Select    End If  Next iEnd SubSub 批量打印()Dim count  As Integercount = ActiveWorkbook.Sheets.countActiveSheet.PageSetup.PrintArea = ""    '取消打印区域For i = 3 To countSheets(i).PrintOutNext iEnd SubSub 批量删除对账单()Dim count  As Integercount = ActiveWorkbook.Sheets.countFor i = count To 3 Step -1Sheets(i).DeleteNext iEnd Sub


 

原创粉丝点击