Add New Sheet when Multiple Sheets are Selected

[Randy]

Hi,
I have a situation where I need to add a new worksheet to a workbook.
Simple enough except that if the user has multiple sheets selected,
this creates an Excel error and it won't add the new sheet. My first
question is if anybody knows of a way to get Excel to allow me to
simply add the new sheet when multiple sheets are selected?

Assuming that there is no way to do this, as a workaround, I decided
to execute the following steps:

1. Capture each selected sheet in a variable
2. Select Sheet1 (this ensures that only one sheet is selected and
avoids the Excel error)
3. Add the new sheet
4. Reselect the sheets initially selected so that the workbook is
in its original state

In step 4, I do not know how to add incremental sheets to the sheets
already selected. If I do

sheet1.select
sheet2.select

then only sheet2 is selected. I know that I can do

Worksheets(Array(sheet1, sheet2)).Select

but this requires me to know exactly which sheets will be selected,
which I don't.

So

'Capture selected sheets in variable
Dim intShtSel(1 To 100) As Integer
Dim x As Integer
Dim intSelShtCount As Integer
intSelShtCount = ActiveWindow.SelectedSheets.Count
x = 1
For Each wks In ActiveWindow.SelectedSheets
intShtSel(x) = wks.Index
x = x + 1
Next

'Add new sheet
wb.Sheets(1).Select
wb.Sheets.Add.Name = "NewSheet"

'Reselect original sheets
Sheets(intShtSel(1)).Select
For x = 2 To intSelShtCount
Sheets(intShtSel(x)).Activate
Next


It's this last section that doesn't work. Can anybody help?

Thanks,
Randy

 

[Dave Peterson]

How about:

Option Explicit
Sub testme01()

Dim mySheets As Sheets
Dim ActSheet As Object
Dim NewWks As Worksheet

Set mySheets = ActiveWindow.SelectedSheets
Set ActSheet = ActiveWindow.ActiveSheet

ActSheet.Select Replace:=True 'single sheet only

Set NewWks = Worksheets.Add
'do anything with that newwks???

mySheets.Select
ActSheet.Activate
End Sub

 

[Randy]

That worked perfectly. Much better than the approach that I was
taking.

Thanks, Dave.

Randy