Hidden sheet problem(VBA)

AllenL · Apr 2nd, 2002, 07:19 AM

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

GeorgeB · Apr 2nd, 2002, 07:27 AM

Hi AllenL
Change .Select to .Activate
George

Dave Hawley · Apr 2nd, 2002, 07:27 AM

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

GeorgeB · Apr 2nd, 2002, 07:30 AM

Sorry AllenL
I misunderstood your post.
.Select will hang on a hidden sheet but .Activate will not.
Have a great day
George

AllenL · Apr 2nd, 2002, 07:38 AM

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

AllenL · Apr 2nd, 2002, 07:47 AM

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

GeorgeB · Apr 2nd, 2002, 08:01 AM

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

Ricky Morris · Apr 2nd, 2002, 08:43 AM

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

Apr 2nd, 2002, 07:27 AM	#2
GeorgeB 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.

Apr 2nd, 2002, 07:27 AM	#3
Dave Hawley 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

Apr 2nd, 2002, 07:30 AM	#4
GeorgeB 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.

Apr 2nd, 2002, 08:01 AM	#7
GeorgeB 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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 2nd, 2002, 07:19 AM	#1
AllenL 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

Apr 2nd, 2002, 07:38 AM	#5
AllenL 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

Apr 2nd, 2002, 07:47 AM	#6
AllenL 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

Apr 2nd, 2002, 08:43 AM	#8
Ricky Morris 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