Runtime Error 1004: Blah blah worksheet class failed

[notout50]

Sorry, but I've spent an hour looking for answers for this problem, and although google returns thousands (probably) of pages of results, I can't find an answer.

I was basically wondering if there are any properties that a worksheet holds that stops it from being selected / copied via vba code. I've manually selected and copied the sheet, but basic vba code fails.

    Dim pNum As Integer
    Dim i As Integer
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    
     Application.ScreenUpdating = False
     
        'Message box to enter the number of projects
            pNum = Application.InputBox _
             (Prompt:="How many projects? If no projects, type '0'", Title:="Projects", Type:=1)
               
             If pNum = 0 Then
                Exit Sub
             Else
             
            'Creating summary worksheet
                'Sheets(2).Select
                wb.Sheets(2).Copy After:=wb.Sheets(2)
                Sheets(3).Name = "Summary"

It was failing at the select line (I inherited this code, no idea why it was being selected), but also fails at the copy line, which looks fine to me.

Any ideas? Thanks.

 

[cb366374]

Sorry I have no time to look at this closer but I had this saved from the other day and thought it could be relevant Copying worksheet programmatically causes run-time error 1004 in Excel

 

[RoryA]

Is the sheet visible?

 

[notout50]

Sorry I have no time to look at this closer but I had this saved from the other day and thought it could be relevant Copying worksheet programmatically causes run-time error 1004 in Excel

I have actually used that exact page and technique in the past, as I was copying multiple sheets several times. In this instance though, I only have 4 sheets in the workbook and am only copying one sheet once.

Rory, you raise an interesting question there. The sheet I'm intending to copy is visable, and is 2nd in order of tabs (I believe this is how the reference number works in vba, it dynamically changes depending on the order of the sheet tabs). But when I referenced it by it's name, it worked.

There are hidden sheets! I hardly slept last night because of hidden sheets. Thanks for alerting me to this.