Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

loop through selected worksheeet

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Loop through selected worksheeets with select case

Loop through selected worksheeets with select case

ResolvedVersion 2002/XP

Jenny has attended:
Excel VBA Intro Intermediate course
Excel VBA Intro Intermediate course
Project Management - Framework & Processes course
Excel VBA Advanced course

Loop through selected worksheeets with select case

Hi

I am trying to loop through selected worksheets, using a select case statement to exclude the unwanted worksheets. I have the below code but it doesn't loop at all; it just updates the active sheet. Could you let me know where I am going wrong? "Copied Sheeet" is the name of the worksheet I want to exclude.

Thank you - Jenny.


Sub LoopWorkSheets()

Dim mySheet As Worksheet

For Each mySheet In Worksheets

Select Case mySheet.Name

Case "CopiedSheet"

Case Else

Range("A3").Font.Color = vbRed

End Select

Next mySheet

End Sub

RE: Loop through selected worksheeets with select case

You need to say:

mySheet.Range("A3").Font.Color = vbRed

If you just say Range("A3")... you get the range on the ActiveSheet.

Althernatively, you could do mySheet.Activate within the loop.

/Roy MacLean

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Hiding Formulae in the Formula Bar

It is possible to protect the contents of a cell reference(s) from amendment by applying cell protection. The contents of the cell reference can also be stopped from displaying in the formula bar.

Step 1: Select Format > Cells > Protection.

Step 2: Tick Hidden option. Ensure Locked is ticked

Step 3: Select Tools > Protection > Protect Sheet

(Ensure "Protect worksheet and contents of locked cells" is ticked)


View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.