![]() |
![]() |
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
![]() |
|
|
Thread Tools | Display Modes |
|
|
#1 |
|
Board Regular
Join Date: Mar 2002
Posts: 67
|
I have this code
Sub SearchAllSheets() For Each Worksheet In Worksheets Sheets(Worksheet.Name).Select Call Main Next Worksheet End Sub which runs the macro for each sheet. I have one hidden sheet in the workbook which I don't want it to be run by the macro. However, when the code reach the hidden sheet I get an error in the line Sheets(Worksheet.Name).Select How can I solve this problem???Please help Thanks in advance |
|
|
|
|
|
#2 |
|
Board Regular
Join Date: Feb 2002
Location: Las Vegas Nevada USA
Posts: 240
|
Hi AllenL
Change .Select to .Activate George
__________________
George Learn to listen. Opportunity sometimes knocks very softly. |
|
|
|
|
|
#3 |
|
Banned
Join Date: Feb 2002
Posts: 1,582
|
Hi Allen
Do you need to select each sheet before running "Main" ? If really yes, then try: Code:
Sub SearchAllSheets()
Dim Wrksheet As Worksheet
For Each Wrksheet In Worksheets
If Wrksheet.Visible = xlSheetVisible Then
Sheets(Worksheet.Name).Select
End If
Call Main
Next Wrksheet
End Sub
|
|
|
|
|
|
#4 |
|
Board Regular
Join Date: Feb 2002
Location: Las Vegas Nevada USA
Posts: 240
|
Sorry AllenL
I misunderstood your post. .Select will hang on a hidden sheet but .Activate will not. Have a great day George
__________________
George Learn to listen. Opportunity sometimes knocks very softly. |
|
|
|
|
|
#5 |
|
Board Regular
Join Date: Mar 2002
Posts: 67
|
Hi George
If I change to Activate. The macro runs the hidden sheet(which I don't want that to happen). Any more idea? Thanks in advance |
|
|
|
|
|
#6 |
|
Board Regular
Join Date: Mar 2002
Posts: 67
|
Hello Dave
I am not really sure what's the difference between select the sheet then run "Main" and not select the sheet then run "Main" All I want to do is to have all the sheet to run "Main" except the hidden sheet I try your code but it gives me an error on the same line I had before Sheets(Worksheet.Name).Select Please Help Thanks in advance |
|
|
|
|
|
#7 |
|
Board Regular
Join Date: Feb 2002
Location: Las Vegas Nevada USA
Posts: 240
|
Try something like this to exclude the sheet.
If Not (sht.Name = "MySheet") Then Sorry can't write the full code. the boss just came in. George
__________________
George Learn to listen. Opportunity sometimes knocks very softly. |
|
|
|
|
|
#8 |
|
Board Regular
Join Date: Mar 2002
Posts: 363
|
Try this
Sub SearchAllSheets() For Each worksheet In Worksheets If worksheet.Visible <> 0 Then Sheets(worksheet.Name).Select 'Your code here End If Next worksheet End Sub |
|
|
|
![]() |
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|