Click here to Skip to main content
11,492,385 members (61,707 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: Excel
How to use this below code ?
i want to select case

Sub SelectedWoksheets()
Dim ws As Worksheet
    For Each ws In ActiveWindow.SelectedSheets
        With ws
            'With Code Here
        End With
    Next ws
End Sub

[Edit]Code block added[/Edit]
Posted 3-Nov-12 6:33am
Edited 3-Nov-12 6:50am
Richard MacCutchan at 3-Nov-12 12:18pm
What do you mean you want to select case? In order to do what exactly? Please edit your question and explain what you are trying to do.
ridoy at 3-Nov-12 16:20pm
totally unclear,elaborate it..

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

If i understand you well, you want to use Select Case ... End Select statement.
Select case ws.Name
    Case "Sheet1"
        'do something
    Case "Sheet2"
        'do something
    Case "Sheet3"
        'do something
    Case Else
        'do something
End Select

But if would like to go through the collection of worksheets to "find" the correct one by its name, better use something like this:
Function GetWoksheet(wshName As String, wbk As Workbook) As Worksheet
Dim wsh As Worksheet
On Error Goto Err_GetWoksheet
Set wsh = wbk.Workseets(wshName)
    Set GetWoksheet = wsh
    Exit Function
    MsgBoxErr.Description, vbExclamation, Err.Number
    Set wsh = Nothing
    Resume Exit_GetWoksheet
End Function

Set wsh =  GetWoksheet("Sheet4", ActiveWorkbook)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 404
1 Maciej Los 266
2 KarstenK 220
3 Sascha Lefèvre 215
4 CPallini 208
0 Sergey Alexandrovich Kryukov 9,943
1 OriginalGriff 8,610
2 Sascha Lefèvre 3,609
3 Maciej Los 3,372
4 Richard Deeming 2,440

Advertise | Privacy | Mobile
Web01 | 2.8.150520.1 | Last Updated 4 Nov 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100