| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing whether a sheet is present
The first time a workbook is opened, i want to replace 2 sheets (BBipads and
TBipads) with 2 other sheets (BBipads1 and TBipads1 respectively) then delete BBipads1 and TBipads1. The '1' replacement sheets are inserted by a third party program. On openings after the ffirst, I want to skip the copy/delete process. My sub works the first time - when it does the copy and delete processes. But on 2nd opening, it gives an error when it tries to select the deleted sheets. I thought my tests for the presence of the sheets would avoid such error but there must be something wrong here. Any help would be greatly appreciated. Thanks, Private Sub Workbook_Open() ' first time the book is opened, replace data sheets Application.DisplayAlerts = False Dim sh As Excel.Worksheet On Error Resume Next Set sh = xlApp.Worksheets("BBipads1") On Error GoTo 0 If sh Is Nothing Then Sheets("BBipads1").Select Cells.Select Selection.Copy Sheets("BBipads").Select Cells.Select ActiveSheet.Paste Sheets("BBipads1").Select ActiveWindow.SelectedSheets.Delete Else: End If On Error Resume Next Set sh = xlApp.Worksheets("TBipads1") On Error GoTo 0 If sh Is Nothing Then Sheets("TBipads1").Select Cells.Select Application.CutCopyMode = False Selection.Copy Sheets("TBipads").Select Cells.Select ActiveSheet.Paste Sheets("TBipads1").Select ActiveWindow.SelectedSheets.Delete Else: End If Application.DisplayAlerts = True ' set to welcome screen Sheets("Welcome").Select End Sub -- Jeff |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
testing whether a sheet is present
Hi Jeff
You could try to do it this way....... The first procedure runs from the Workbook_Open() event and calls another procedure TestExists passing the name of the unwanted book as a parameter. If the TestExists procedure finds the worksheet it copies it to a new sheet, this new sheet is then named by removing the last character from the parameter passed (BBipads1 becomes BBipads) and the original sheet is deleted. In the Workbook_Open() procedure you should call each sheet in turn as shown, and of course add more if required. Private Sub Workbook_Open() Call TestExists("BBipads1") Call TestExists("TBipads1") End Sub Sub TestExists(sName As String) Dim wS As Worksheet Application.ScreenUpdating = False For Each wS In Worksheets If wS.Name = sName Then wS.Copy After:=wS ActiveSheet.Name = Mid(wS.Name, 1, Len(wS.Name) - 1) Application.DisplayAlerts = False wS.Delete Application.DisplayAlerts = True End If Next wS Application.ScreenUpdating = True End Sub -- Cheers Nigel "Jeff" wrote in message ... The first time a workbook is opened, i want to replace 2 sheets (BBipads and TBipads) with 2 other sheets (BBipads1 and TBipads1 respectively) then delete BBipads1 and TBipads1. The '1' replacement sheets are inserted by a third party program. On openings after the ffirst, I want to skip the copy/delete process. My sub works the first time - when it does the copy and delete processes. But on 2nd opening, it gives an error when it tries to select the deleted sheets. I thought my tests for the presence of the sheets would avoid such error but there must be something wrong here. Any help would be greatly appreciated. Thanks, Private Sub Workbook_Open() ' first time the book is opened, replace data sheets Application.DisplayAlerts = False Dim sh As Excel.Worksheet On Error Resume Next Set sh = xlApp.Worksheets("BBipads1") On Error GoTo 0 If sh Is Nothing Then Sheets("BBipads1").Select Cells.Select Selection.Copy Sheets("BBipads").Select Cells.Select ActiveSheet.Paste Sheets("BBipads1").Select ActiveWindow.SelectedSheets.Delete Else: End If On Error Resume Next Set sh = xlApp.Worksheets("TBipads1") On Error GoTo 0 If sh Is Nothing Then Sheets("TBipads1").Select Cells.Select Application.CutCopyMode = False Selection.Copy Sheets("TBipads").Select Cells.Select ActiveSheet.Paste Sheets("TBipads1").Select ActiveWindow.SelectedSheets.Delete Else: End If Application.DisplayAlerts = True ' set to welcome screen Sheets("Welcome").Select End Sub -- Jeff |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
testing whether a sheet is present
Jeff,
Do both tests fail or just the second. If the latter, it might be because sh still has the previous value, so clear it before the second test Set sh = Nothing also you can simplify the code somewhat Private Sub Workbook_Open() Dim sh As Excel.Worksheet ' first time the book is opened, replace data sheets Application.DisplayAlerts = False On Error Resume Next Set sh = xlApp.Worksheets("BBipads1") On Error GoTo 0 If sh Is Nothing Then sh.Cells.Copy Sheets("BBipads").Select Cells.Select ActiveSheet.Paste sh.Delete End If Set sh = Nothing On Error Resume Next Set sh = xlApp.Worksheets("TBipads1") On Error GoTo 0 If sh Is Nothing Then Sheets("TBipads1").Cells.Copy Sheets("TBipads").Select Cells.Select ActiveSheet.Paste sh.Delete End If Set sh = Nothing Application.CutCopyMode = False Application.DisplayAlerts = True ' set to welcome screen Sheets("Welcome").Select End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... The first time a workbook is opened, i want to replace 2 sheets (BBipads and TBipads) with 2 other sheets (BBipads1 and TBipads1 respectively) then delete BBipads1 and TBipads1. The '1' replacement sheets are inserted by a third party program. On openings after the ffirst, I want to skip the copy/delete process. My sub works the first time - when it does the copy and delete processes. But on 2nd opening, it gives an error when it tries to select the deleted sheets. I thought my tests for the presence of the sheets would avoid such error but there must be something wrong here. Any help would be greatly appreciated. Thanks, Private Sub Workbook_Open() ' first time the book is opened, replace data sheets Application.DisplayAlerts = False Dim sh As Excel.Worksheet On Error Resume Next Set sh = xlApp.Worksheets("BBipads1") On Error GoTo 0 If sh Is Nothing Then Sheets("BBipads1").Select Cells.Select Selection.Copy Sheets("BBipads").Select Cells.Select ActiveSheet.Paste Sheets("BBipads1").Select ActiveWindow.SelectedSheets.Delete Else: End If On Error Resume Next Set sh = xlApp.Worksheets("TBipads1") On Error GoTo 0 If sh Is Nothing Then Sheets("TBipads1").Select Cells.Select Application.CutCopyMode = False Selection.Copy Sheets("TBipads").Select Cells.Select ActiveSheet.Paste Sheets("TBipads1").Select ActiveWindow.SelectedSheets.Delete Else: End If Application.DisplayAlerts = True ' set to welcome screen Sheets("Welcome").Select End Sub -- Jeff |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
testing whether a sheet is present
thanks very much Nigel and Bob.
Bob, it was the first one failing. Setting sh=nothing didn't solve the problem. This approach below is working. Thanks, Sub TestExists(sName As String) Dim wS As Excel.Worksheet Application.CutCopyMode = False Application.ScreenUpdating = False For Each wS In Worksheets If wS.Name = sName Then Sheets(sName).Select Cells.Select Selection.Copy Sheets(Mid(wS.Name, 1, Len(wS.Name) - 1)).Select Cells.Select ActiveSheet.Paste Sheets(sName).Select Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True End If Next wS Application.ScreenUpdating = True End Sub Private Sub Workbook_Open() ' first time the book is opened, replace data sheets Call TestExists("BBipads1") Call TestExists("TBipads1") ' set to welcome screen Sheets("Welcome").Select End Sub -- Jeff "Jeff" wrote: The first time a workbook is opened, i want to replace 2 sheets (BBipads and TBipads) with 2 other sheets (BBipads1 and TBipads1 respectively) then delete BBipads1 and TBipads1. The '1' replacement sheets are inserted by a third party program. On openings after the ffirst, I want to skip the copy/delete process. My sub works the first time - when it does the copy and delete processes. But on 2nd opening, it gives an error when it tries to select the deleted sheets. I thought my tests for the presence of the sheets would avoid such error but there must be something wrong here. Any help would be greatly appreciated. Thanks, Private Sub Workbook_Open() ' first time the book is opened, replace data sheets Application.DisplayAlerts = False Dim sh As Excel.Worksheet On Error Resume Next Set sh = xlApp.Worksheets("BBipads1") On Error GoTo 0 If sh Is Nothing Then Sheets("BBipads1").Select Cells.Select Selection.Copy Sheets("BBipads").Select Cells.Select ActiveSheet.Paste Sheets("BBipads1").Select ActiveWindow.SelectedSheets.Delete Else: End If On Error Resume Next Set sh = xlApp.Worksheets("TBipads1") On Error GoTo 0 If sh Is Nothing Then Sheets("TBipads1").Select Cells.Select Application.CutCopyMode = False Selection.Copy Sheets("TBipads").Select Cells.Select ActiveSheet.Paste Sheets("TBipads1").Select ActiveWindow.SelectedSheets.Delete Else: End If Application.DisplayAlerts = True ' set to welcome screen Sheets("Welcome").Select End Sub -- Jeff |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Locating if a Sheet is present in a workbook | Excel Discussion (Misc queries) | |||
| getting the sheet names present in a work book???? | Excel Programming | |||
| finding a value thats present more than once in a sheet | Excel Programming | |||
| testing if a sheet is protected | Excel Programming | |||
| Testing to see if a sheet name exists | Excel Programming | |||
Linear Mode
