VBA code works in new workbook, but not the one I want it to work in

Question

I have a workbook and the macro doesn't work, basically just copying from cell A3 and down with values in each worksheet, then pasting each of that into a new summary worksheet in succession.

When I literally create a new workbook and copy and paste all my sheets into the new workbook everything works fine. But If I stay in the old work book it gives the error

Select Method of Worksheet class failed

It didn't fail in the other 2 workbooks I created with the exact same sheets that I copied over...why this one particular workbook?

I close all other workbooks to avoid error with ActiveWorkBook - perhaps not the best way of doing things, but it shouldn't be affecting this.

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

1) No need to select - see this question, and 2) See this question for how to find the last row: Range("A3", Range("A3").SpecialCells(xlCellTypeLastCell)).Select is problematic. — BigBen
Check to see if ActiveWorkbook is null before you continue your code. — Frank Ball

Vitaliy Prushak Vitaliy Prushak · Accepted Answer · 2019-11-19T17:42:17

Select Method of Worksheet class failed

This may be caused by worksheet visibility - excel cannot select hidden or very hidden worksheet. Try this code to check whether some sheets are not visible

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

And here how you can refactor your code to avoid issues with ActiveWorkbook/ActiveSheet statements, and without using .Select/.Activate methods:

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

VBA code works in new workbook, but not the one I want it to work in

1 Answers