Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add New Sheet when Multiple Sheets are Selected
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add New Sheet when Multiple Sheets are Selected
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add New Sheet when Multiple Sheets are Selected
That worked perfectly. Much better than the approach that I was
taking. Thanks, Dave. Randy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Scroll Lock for selected multiple sheets | Excel Worksheet Functions | |||
How to tell if multiple sheets are selected? | Excel Programming | |||
How do I undo an action when multiple sheets are selected? | Excel Worksheet Functions | |||
Multiple sheets selected | Excel Discussion (Misc queries) | |||
Excluding sheet from group of selected sheets | Excel Programming |