VBA代码在新工作簿中工作，但不是我希望它在其中工作的工作簿

Question

我有一个工作簿，宏不起作用，基本上只是从单元格A3上下复制每个工作表中的值，然后连续将每个值粘贴到新的摘要工作表中。

当我真的创建了一个新工作簿，并将我所有的工作表复制粘贴到新工作簿中时，一切都很好。但如果我继续写旧的工作手册，就会出现错误

选择工作表类的方法失败

在我使用与我复制的完全相同的工作表创建的其他2个工作簿中，它没有失败...为什么是这个特定的工作簿？

我关闭所有其他工作簿以避免ActiveWorkBook出错-也许这不是最好的方式，但它不应该影响这一点。

Option Explicit

Public Sub SelectItemsEstimate()
    Dim ws As Worksheet
    Dim LastRow As Long

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Business Unit Key" _
            And ws.Name <> "dv" And ws.Name <> "cc" And ws.Name <> "wer" And ws.Name <> "dafd" _
            And ws.Name <> "Master Sheet Summary Data" _
            And ws.Name <> "Query for Macro" _
            And ws.Name <> "Query for Macro 2 with Format" _
            And ws.Name <> "Paste all values" _
            And ws.Name <> "Summary" Then

            Worksheets(ws.Name).Select
            Range("A3", Range("A3").SpecialCells(xlCellTypeLastCell)).Select
            Selection.Copy

            With ActiveWorkbook.Worksheets("Summary")
                LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row ' get last row with data in column "A"
                ' paste
                .Range("A" & LastRow + 1).PasteSpecial Paste:=xlPasteValues
            End With
        End If
    Next
End Sub

Answer 1

选择工作表类的方法失败

这可能是由工作表可见性引起的-excel无法选择隐藏或非常隐藏的工作表。尝试此代码检查某些工作表是否不可见

Sub CheckSheetVisibility()
Dim ws As Worksheet
Dim wb As Workbook

Set wb = ThisWorkbook

For Each ws In wb.Sheets
    If Not ws.Visible = xlSheetVisible Then ws.Visible = xlSheetVisible
Next
End Sub

下面介绍如何重构代码以避免ActiveWorkbook/ActiveSheet语句出现问题，并且不使用。选择/。激活方法：

Sub SelectItemsEstimate()
Dim ws As Worksheet
Dim wb As Workbook
Dim wsToCopyTo As Worksheet
Dim LastRow As Long

Set wb = ThisWorkbook
Set wsToCopyTo = wb.Sheets("Summary")

For Each ws In wb.Sheets
    If ws.Name <> "Business Unit Key" _
            And ws.Name <> "dv" And ws.Name <> "cc" And ws.Name <> "wer" And ws.Name <> "dafd" _
            And ws.Name <> "Master Sheet Summary Data" _
            And ws.Name <> "Query for Macro" _
            And ws.Name <> "Query for Macro 2 with Format" _
            And ws.Name <> "Paste all values" _
            And ws.Name <> "Summary" Then

            With ws
                Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp)).Copy
            End With

            wsToCopyTo.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

    End If
Next

End Sub