Help with code Please

[Bagsy]

Can any body please help with this code I am trying to get to work.
What I am trying to do is select all the sheets in a workbook with the exception of certain named sheets..
The code is an example just to try to get it to work, the actual workbook has 76 named sheets of which there will be at least 7 sheets I do not wish to select.
Each time I run this code I get an error Object variable or With block variable not set (Error 91) I have looked in help but I am not sure what set statement I require. (if that is indeed the problem)

As always
All help is very much appreciated.

Code Below

Sub test()
Dim sh As Worksheet
Dim MyObject As Object
Set MyObject = Sheets()

If sh.Name <> Sheets("sheet3") And sh.Name <> Sheets("Sheet6") Then

Worksheets.Select

End If

End Sub

 

[erik.van.geit]

wasn't the purpose to write to most of the sheets and exclude only some of them ?

each sheetname written in the code will be excluded of any action
example
assumptions
sheets A, B, C, D, E, F
code If IsError ... "B", "D"
following sheets will be changed
A, C, E, F

if you want the inverse (operations on B and D), I think you can code
If Not IsError ....

best regards,
Erik

 

[erik.van.geit]

just reviewed your code

    'Enter code here 
        Range("D19").Select 
    ActiveCell.FormulaR1C1 = "123" 
    Range("D20").Select 
    ActiveCell.FormulaR1C1 = "456" 
    Range("D21").Select

IMPORTANT REMARK
since you don't specify the sheet, all your code is refering to the activesheet
you need
sh.Range("....
you don't have to select the cells (it won't even work in this case without selecting the sheet also)
so use this syntax

    sh.Range("D19") = "123"
    sh.Range("D20") = "456"

 

[Bagsy]

Hi Erik
Thanks for getting back to me.
Yes that is exactly what I require, I have hard coded the sheets I do not wish to select, but cannot get it to work.
What it is doing It is changing just the “Top Sheet” , which is one of the sheets I have put into the array not to select. None of the other sheets in the workbook are being changed.
Please see code below. I can’t see what I have done wrong

Sub exclude_sheets()
'Erik Van Geit
'050904
'do some operations with all sheets, except some

Dim SheetsArray As Variant
'Change sheet names as required
SheetsArray = Array("Top Sheet", "Equipment Utilised", "Graph", "Quote", "Sort Sheet", "MC & HB Serial Nos")

For Each sh In ThisWorkbook.Worksheets
    If IsError(Application.Match(sh.Name, SheetsArray, 0)) Then
    'Enter code here
        Range("D19").Select
    ActiveCell.FormulaR1C1 = "test"
    Range("D20").Select
    ActiveCell.FormulaR1C1 = "test aswell"
    Range("D21").Select
    Sheets("Top sheet").Select
    
    
    End If
Next sh

End Sub

 

[Bagsy]

Many Thanks Erik
That’s cracked it, I just had to change the line thisworkbook to activeworkbook as well.
All working OK now.