testing whether a sheet is present

[Guest]

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

 

[Nigel]

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

 

[Bob Phillips]

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)

 

[Guest]

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