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

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.



"Scott" wrote in message
...
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

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


--
Zack Barresse
------------------------------------------------------------------------
Zack Barresse's Profile: http://www.thecodecage.com/forumz/member.php?userid=119
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158608

Microsoft Office Help