已在使用的控制变量 - 简单的 VBA 脚本-解网

问：

为此使用控制变量，我猜是因为“ws”，有人可以帮助解决吗？谢谢

Sub CreateNewWBS()
    Dim wbThis As Workbook
    Dim wbNew As Workbook
    Dim ws As Worksheet
    Dim strFilename As String
    Set wbThis = ThisWorkbook
    For Each ws In wbThis.Worksheets
        strFilename = wbThis.Path & "/" & ws.Name
        ws.Copy
        Set wbNew = ActiveWorkbook
        For Each ws In wbNew.Worksheets
            ws.Cells.Copy
            ws.Cells.PasteSpecial xlPasteValues

        wbNew.SaveAs strFilename
        wbNew.Close
    Next ws
End Sub

Excel VBA

Sub CreateNewWBS()
Dim wbThis As Workbook
Dim wbNew As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim strFilename As String
Set wbThis = ThisWorkbook
For Each ws In wbThis.Worksheets
    strFilename = wbThis.Path & "/" & ws.Name
    ws.Copy
    Set wbNew = ActiveWorkbook
        For Each ws2 In wbNew.Worksheets
            ws2.Cells.Copy
            ws2.Cells.PasteSpecial xlPasteValues
        Next
    wbNew.SaveAs strFilename
    wbNew.Close
Next ws
End Sub

1赞 Chronocidal 7/30/2020 #2

下面是代码的简化版本：

Sub CreateNewWBs()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        ws.Copy
        With ActiveWorkbook
            .Worksheets(1).UsedRange.Value = .Worksheets(1).UsedRange.Value
            .SaveAs ThisWorkbook.Path & "/" & ws.Name
            .Close
        End With
    Next ws
End Sub

我已经删除了第二个循环，它给您带来了问题（既因为您没有，也因为您试图重用现有变量），因为该循环仅包含 1 个工作表，替换为，并交换了，以避免需要剪贴板。ForForNextwbNewwbThisThisWorksheet.Copy:.PasteSpecial.Value=.Value

上一个：VBA 崩溃大约 700 次迭代 - 当我尝试将此 VBA 循环运行到 1000 秒（650 次是安全的）迭代时，Excel 不断崩溃。有什么想法吗？

下一个：基于列表隐藏工作表

已在使用的控制变量 - 简单的 VBA 脚本

Control variable already in use - simple VBA script

评论