vba实例保存

最新推荐文章于 2024-05-23 15:03:02 发布

ldq_sd

最新推荐文章于 2024-05-23 15:03:02 发布

阅读量475

点赞数

文章标签： excel

我的网站：https://www.mihaoyun.com

本文链接：https://blog.csdn.net/ldq_sd/article/details/129598131

版权

每个sheet存到一个新文件中

Sub chaifen()
   Dim sht As Worksheet
       Dim MyBook As Workbook
       Set MyBook = ActiveWorkbook
       For Each sht In MyBook.Sheets
           sht.Copy
           ActiveWorkbook.SaveAs Filename:=MyBook.Path & "\" & MyBook.Name & "_" & sht.Name & ".xls"
           ActiveWorkbook.Close
       Next
       MsgBox "文件已经被分拆完毕!"
End Sub

按照每20行进行切割，切割后存到新的excel表中。

Sub splitRow()
Application.ScreenUpdating = False
p = ActiveWorkbook.Path & "\"
With ActiveSheet
    For r = 1 To .Range("a1048576").End(xlUp).Row Step 20
        Set wb = Workbooks.Add
        .Rows(r).Resize(30).Copy wb.Sheets(1).Cells(1)
        wb.SaveAs p & r & ".xls", xlNormal
        wb.Close
    Next
End With
Application.ScreenUpdating = True
End Sub

循环目标区域，如果对应名字的sheet已经有了，就不创建，否则创建对应的sheet

Sub process()
Dim sht As Worksheet
Dim i, k As Integer
For Each rag In Range("d2:d1000")
    k = 0
    For Each sht In Sheets
        If rag.Value = sht.Name Then
            k = 1
            Exit For
        End If
    Next
    If k = 0 Then
        Sheets.Add after:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = rag
    End If
Next
End Sub