Help with code Please

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
467
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
I don't see anywhere you actually set sh to anything.

This will run/compile but I'm not sure it'll actually do what you want because I'm not actually sure what you want to do.:)
Code:
Sub test()
Dim sh As Worksheet
Dim MyObject As Object
    Set MyObject = Sheets()
    
    For Each sh In MyObject
        If sh.Name <> "sheet3" And sh.Name <> "Sheet6" Then
    
            Worksheets.Select
    
        End If
    Next sh

End Sub
 
Upvote 0
Thanks for the help Norrie
What I need to do in this example, is select all the sheets in this workbook with the exception of Sheets 3 & 6.
When I run the code you have just posted for me it still selects all of the sheets in the workbook. I have looked at some of your other posts on this type of thing where you recommend looping through the worksheets. Its just I really would like to know why I cannot get this code to work.
Its something I thought would be easy but has turned out to be a real problem
 
Upvote 0
The reason it selects all the sheets is because that's what this does.
Code:
Worksheets.Select
Why do you want to select multiple sheets?
 
Upvote 0
Hi, would this help ?
Code:
Sub exclude_sheets()
'Erik Van Geit
'050904
'do some operations with all sheets, except some

Dim SheetsArray As Variant
SheetsArray = Array("Input Sheet", "Consultant", "No Benefits", "Benefits")

For Each sh In ThisWorkbook.Worksheets
    If IsError(Application.Match(sh.Name, SheetsArray, 0)) Then
    'code
    End If
Next sh

End Sub
kind regards,
Erik
 
Upvote 0
Thanks Norrie
The reason I wanted to select certain sheets is I have some code which I have adapted from help I have received from this board.
It lists all the files in a directory, and opens each one up in turn, And runs a macro that I have recorded to make certain changes fix errors etc.. Repeating on all templates in the directory.
These templates have 76 sheets 70 of which are reports & are identical. These are for recording data, the other sheets are Top sheet , quote, Graphs, Sort Sheet, etc.
When I record the macro to make any amendment I select all the reports sheets and make the changes all at once.
This was really messy when you look at the recorded code & I was attempting to tidy it up
 
Upvote 0
Bagsy

You can use either the code I posted or the code Erik posted to loop through sheets.

In Erik's code just replace the names in SheetArray with the sheets you want to miss.

As to adapting the recorded macro, that really depends on what it's doing.

You say you were selecting multiple sheets, were you then making changes to them while they were selected?
 
Upvote 0
Hi,

is this still a question ?
did you see my code ?
or wasn't this what you needed ?

would you like to group the sheets within the code to run it faster ??
this can only be necessary if the changes are identical for all sheets

best regards,
Erik
 
Upvote 0
Thanks Norrie & Erik
Sorry Erik posted my reply at the same time you posted.
I think your code will work well I will post back if I have any more problems if that is OK.

Have a great weekend and once again many thanks
 
Upvote 0
Norrie Erik
I hope you don’t mind just helping out a bit more
I am having a problem with the code that Erik kindly wrote. I can only get it to make changes to the “Top Sheet” in this case cells D19, D20, where I need it to select each sheet in the workbook with the exception of the sheets in the Array which includes the top sheet. I’m not at all sure what I have done wrong.
The “Top sheet” is the first sheet in the workbook.


Code:
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 = "123"
    Range("D20").Select
    ActiveCell.FormulaR1C1 = "456"
    Range("D21").Select
    
    
    
    End If
Next Sh

End Sub
 
Upvote 0

Forum statistics

Threads
1,223,431
Messages
6,172,075
Members
452,444
Latest member
ShaImran193

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top