Deselect a single sheet

[Scott]

Hello,

I was wondering if it was possible to deselect a single sheet in a
workbook.

My code will receive a workbook with all sheets selected. There are
dozens to hundreds of sheets. I want to just unselect a single
sheet. Is there a quick way to do this.

For example, to select all sheets you can say: workbook.sheets.select

This doesnt work, but is there something like workbook.sheets
(3).deselect

Any help would be great.

Thanks

 

[JLGWhiz]

Say you receive a wb with sheets 1 thru 3 selected but you do not wan sheet
2 to be selected.

Sheets(Array("Sheet1", "Sheet3")).Select

Now sheet2 has been deselected. You can do the same thing manually by
holding Ctrl and clicking the sheet2 tab.

 

[Zack Barresse]

Hi there!

You could use something like this ...

Option Explicit

Sub DeSelectSheet(wsName As String)
Dim aSheets() As Variant, i As Long, iCnt As Long
Dim ws As Worksheet, wsCurr As Worksheet
ReDim aSheets(1 To ActiveWindow.SelectedSheets.Count)
Set wsCurr = ActiveSheet
For Each ws In ActiveWindow.SelectedSheets
If wsName <> ws.Name Then
iCnt = iCnt + 1
aSheets(iCnt) = ws.Name
End If
i = i + 1
Next ws
If UBound(aSheets) = 1 Then Exit Sub
If i <> iCnt Then
ReDim Preserve aSheets(1 To iCnt)
End If
If ActiveSheet.Name = wsName Then
Set wsCurr = ActiveWorkbook.Sheets(aSheets(1))
End If
wsCurr.Select
Sheets(aSheets()).Select
wsCurr.Activate
End Sub

Sub TestDeSelectPlease()
Call DeSelectSheet("Sheet1")
End Sub

So you would call the first procedure, passing the sheet name as a
string. It would need to be the activeworkbook as well.

HTH