VBA 生成新的表格

最新推荐文章于 2024-08-17 08:32:30 发布

feng547

最新推荐文章于 2024-08-17 08:32:30 发布

阅读量1.7k

点赞数 1

文章标签： vba integer 工作

本文链接：https://blog.csdn.net/feng547/article/details/6717663

版权

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 i

End Sub

Sub 批量打印()
Dim count  As Integer
count = ActiveWorkbook.Sheets.count
ActiveSheet.PageSetup.PrintArea = ""    '取消打印区域
For i = 3 To count
Sheets(i).PrintOut
Next i
End Sub


Sub 批量删除对账单()
Dim count  As Integer
count = ActiveWorkbook.Sheets.count
For i = count To 3 Step -1
Sheets(i).Delete
Next i
End Sub