Runtime 1004 Select method of Worksheet class failed (1 Viewer)

[veraloopy]

Hi

Sorry if i've posted this in the wrong forum but I couldn't find one specific to excel.

I have a workbook with several sheets called "Instructions", "Summary", "Template" and then several other sheets labelled "Company1", "Company2", "Company3", etc.

I keep getting a Runtime 1004 Select method of Worksheet class failed error on the line in the script highlighted below.

To explain the purpose, the script should clear all content except row A in the Summary tab. Then it should copy A109 to CH109 from each tab (except the first 3) in turn into the first blank row in the summary tab. Depending on the group, there could be any number of company tabs.
Once it's done all the tabs, it should end

Application.ScreenUpdating = False
myCount = Sheets.Count
Sheets("Summary").Select
Range("A2:CH2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
ActiveSheet.Next.Select
s = ActiveSheet.Name
For i = 4 To myCount
Sheets(s).Select
ActiveSheet.Next.Select 'FAILS HERE
s = ActiveSheet.Name
Range("A109:CH109").Select
Selection.Copy
Sheets("Summary").Select
Range("A65536").Select
Selection.End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Next i
Application.ScreenUpdating = True
End Sub


I've been trying several help options and looking for similar forum posts but all errors of the same code seem to point at generic 'select' errors

Any help would be greatly appreciated

 

[boblarson]

There is an Excel forum and I'm moving this there.

Also this:

ActiveSheet.Next.Select 'FAILS HERE

should just be

NEXT

 

[boblarson]

But what is the point of this:

For i = 4 To myCount
Sheets(s).Select
ActiveSheet.Next.Select 'FAILS HERE


You aren't doing anything except selecting them. Why???

 

[chergh]

Application.ScreenUpdating = False
myCount = Sheets.Count
Sheets("Summary").Select
Range("A2:CH2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
ActiveSheet.Next.Select
s = ActiveSheet.Name
For i = 4 To myCount
Sheets(s).Select
ActiveSheet.Next.Select 'FAILS HERE
s = ActiveSheet.Name
Range("A109:CH109").Select
Selection.Copy
Sheets("Summary").Select
Range("A65536").Select
Selection.End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Next i
Application.ScreenUpdating = True
End Sub

.........

 

[chergh]

Now something a little more helpful. Best way to solve issues with select statement is to not use them.

Sub blah()

Dim ws As Worksheet
Dim summSheet As Worksheet
Dim lastRow As Long

Set summSheet = ThisWorkbook.Worksheets("Sheet1")


lastRow = summSheet.UsedRange.Rows(summSheet.UsedRange.Rows.Count).Row
        
summSheet.Range("A2:CH" & lastRow).ClearContents



For Each ws In ThisWorkbook.Worksheets

    Select Case ws.Name
    
        Case Is = "Instructions", "Summary", "Template"
        
        Case Else
        
            ws.Range("A109:CH109").Copy
            
            lastRow = summSheet.UsedRange.Rows(summSheet.UsedRange.Rows.Count).Row
            
            summSheet.Paste summSheet.Range("A" & lastRow + 1)
                
    
    End Select

Next ws

End Sub

THough it was that activesheet.next is invalid that caused the issue afaik there is no next method for the sheet object. The code you have is really bad but well done for trying. Any issues with the above let me know.

 

[veraloopy]

Thanks Bob for pointing me in the right direction of the Excel forum

Chergh, Many thanks for the above code which I've applied and now goes a little further now, but on the 'Summary' page I think I need it to paste special as it's showing me the #REF! error as if it's looking for the formula instead.

Where would I put the PasteSpecial command?

Thanks again for all your help, very much appreciated

 

[chergh]

Like this, the change is highlighted in blue.

Sub blah()

Dim ws As Worksheet
Dim summSheet As Worksheet
Dim lastRow As Long

Set summSheet = ThisWorkbook.Worksheets("Sheet1")


lastRow = summSheet.UsedRange.Rows(summSheet.UsedRange.Rows.Count).Row
        
summSheet.Range("A2:CH" & lastRow).ClearContents



For Each ws In ThisWorkbook.Worksheets

    Select Case ws.Name
    
        Case Is = "Instructions", "Summary", "Template"
        
        Case Else
        
            ws.Range("A109:CH109").Copy
            
            lastRow = summSheet.UsedRange.Rows(summSheet.UsedRange.Rows.Count).Row
            
            [COLOR="Blue"]summSheet.Range("A" & lastRow + 1).pastespecial xlPasteValuesAndNumberFormats[/COLOR]
                
    
    End Select

Next ws

End Sub

 

[veraloopy]

Hi Chergh

Working great! The only thing it does now on the 'Summary' page is when it pastes, it seems to remember that rows 2-6 were previously used (although the contents were cleared) and when a 2nd paste is done, it puts the summary into rows 7-11

Any ideas?

Thanks again

 

[chergh]

There might be a blank string or something in one of the cells. try changin the line where contents are cleared to:

summSheet.Range("A2:IV" & lastRow).ClearContents

 

[veraloopy]

Hi
No still has the same effect....

 

[chergh]

Try changing the clearcontents to delete

 

[veraloopy]

Brilliant!!
That worked perfect

Many thanks for all your help, very much appreciated

 

[veraloopy]

Hi chergh

Sorry to be a pain again

I'm getting the cells A109:CH109 copied from the worksheets = "Instructions", "Summary", "Template"

These ones should be excluded, do I need something after this line:


Case Is = "Instructions", "Summary", "Template"

to say - if the sheet name is equal to these 3 then exclue them from the copy/paste?


Thanks in advance again

 

[chergh]

I forgot about this. The code is correct just make sure that the case and spelling is correct for your worksheets.

 

[Brianwarnock]

Should this

Case Is = "Instructions", "Summary", "Template"

be

Case "Instructions", "Summary", "Template"

Brian

 

[chergh]

That shouldn't make any difference afaik brian.

 

[Brianwarnock]

I find the help explanation of the is keyword confusing, I only know that their example shows things as per my suggestion and that I have only used is with a single comparison and > eg is > 8,
but I wouldn't argue with anybody, never mind you , about the correct syntax.

BTW I loved the code it is so neat.

Brian

 

[chergh]

I find the help explanation of the is keyword confusing, I only know that their example shows things as per my suggestion and that I have only used is with a single comparison and > eg is > 8,
but I wouldn't argue with anybody, never mind you , about the correct syntax.

BTW I loved the code it is so neat.

Brian

I think what you suggested is the normal way of doing it and I use "is =" out of habit.

I just wish my house was as neat as my code.

 

[veraloopy]

Cool
Thanks for getting back to me, that works now

Many thanks again for all your help