• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Already used VBA code stopped working - Worksheets(i).Select (False)

nagovind

nagovind

Member
Dear All,

I was using this code for long time as advised by Hui, suddenly it stopped working in MS Excel 2013

For i = 1 To Sheets.Count
Worksheets(i).Select (False)
Next
The above code is not selecting all the sheets

Please do the needful

Regards
Govind
 
This worked for me.
Code: 
Sub Test()
For i = 1 To Worksheets.Count
Worksheets(i).Select (False)
Next
End Sub

If that doesn't work... try.
Code: 
Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Select False
Next

End Sub
 
Hi Chihiro,

Thanks for your help. I tried this code but same issue exists from yesterday
Please help

Sub Test()
Dim ws As Worksheet
ForEach ws In ThisWorkbook.Worksheets
ws.SelectFalse
Next

EndSub

regards
Govind
 
For i = 0 To Sheets.Count - 1
If Left(Sheets(i + 1).Name, 4) <> "SKIP" Then 'Set criteria here
ReDim Preserve ShtArr(UBound(ShtArr, 1) + 1)
ShtArr(UBound(ShtArr, 1)) = Sheets(i + 1).Name
End If
Next
Sheets(ShtArr()).Select

Is there is any way to use as it above
But i'm getting subscript out of range in "Sheets(ShtArr()).Select" or "Sheets(ShtArr).Select" is not working to select the sheets that is stored in the array
 
Try this.
Code: 
Sub test()
Dim wsDict As Object, i as Integer
Set wsDict = CreateObject("Scripting.Dictionary")

For i = 1 To Worksheets.Count
    wsDict.Add Worksheets(i).Name, CStr(i)
Next

Worksheets(wsDict.Keys).Select
End Sub
 
Hi Chihiro,

Sub test()
Dim wsDict AsObject, i asInteger
Set wsDict = CreateObject("Scripting.Dictionary")

For i = 1 To Worksheets.Count
wsDict.Add Worksheets(i).Name, CStr(i)
Next

Worksheets(wsDict.Keys).Select
EndSub

In the above code, once it is executed, after for loop and before selecting the sheets that is stored in "wsDict.Keys", i need to have a condition that if Worksheets(1).Name = "APPLE" then i need to "Deselect" that worksheet 1, please advise

Regards
Govind
 
Code: 
Sub test()
Dim wsDict AsObject, i asInteger
Set wsDict = CreateObject("Scripting.Dictionary")

For i = 1 To Worksheets.Count
wsDict.Add Worksheets(i).Name, CStr(i)
Next

Worksheets(wsDict.Keys).Select
EndSub

Sorry here it is
 
Actually i need the logic to execute the code after running the for loop with "wsDict.Add Worksheets(i).Name, CStr(i)"
 
Code: 
If Worksheets(1).Name <> "APPLE" then wsDict.Add Worksheets(1).Name, CStr(1)
For i = 2 To Worksheets.Count
    wsDict.Add Worksheets(i).Name, CStr(i)
Next

Why u are looking to select the sheets. If we know the reason then might share better way.
 
Something like this.
Code: 
Sub test()
Dim wsDict As Object, i As Integer
Set wsDict = CreateObject("Scripting.Dictionary")

For i = 1 To Worksheets.Count
    If Worksheets(i).Name <> "APPLE" Then wsDict.Add Worksheets(i).Name, CStr(i)
Next

Worksheets(wsDict.Keys).Select
End Sub
 
But if it is required to select more than one sheet after selection it is completed

Say i have selected all sheets, out of selected sheet is there is any way to deselect more than 3 sheets with specific different names?

Kindly advise
Thanks
 
That is roundabout way do doing things. And really, just adds extra step in code.
However, you can try something like below
Assuming you have multiple/All sheets selected... and wanting to deselect "Apple", "Orange" and "Banana".
Code: 
Sub UnselectSheetName()
Dim ws As Worksheet

With CreateObject("Scripting.Dictionary")
    For Each ws In ActiveWindow.SelectedSheets
        If Not IsNumeric(Application.Match(ws.Name, Array("Apple", "Orange", "Banana"), 0)) Then
            .Item(ws.Name) = 1
        End If
    Next
    Worksheets(.Keys).Select
    .RemoveAll
End With

End Sub
 
You must log in or register to reply here.
Back
Top