try [code] next
 

Hi,
in the beginning of my code, several worksheets are being created:
ActiveWorkbook.Sheets.Add.Name = "Information_Database_Searches"
ActiveWorkbook.Sheets.Add.Name = "Information_Holdings"
ActiveWorkbook.Sheets.Add.Name = "Information_AgR_Publications"
ActiveWorkbook.Sheets.Add.Name = "Information_Pres_Reports"
ActiveWorkbook.Sheets.Add.Name = "Information_IntellProp"

For testing, I want to re-run the macro, but I always have to delete
these already created sheets so that the macro runs properly.

How can I write VBA, which first looks of these sheets are already
there. If not - create them, if yes, then go on with next section of
code.

Cheers
Juergen


--
juergenkemeter
------------------------------------------------------------------------
juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248
View this thread: http://www.excelforum.com/showthread...hreadid=500888

try [code] next
 

How about just deleting them to start.

with activeworkbook
on error resume next
application.displayalerts = false
.sheets("Information_Database_Searches").delete
.sheets("Information_Holdings").delete
.sheets("Information_AgR_Publications").delete
.sheets("Information_Pres_Reports").delete
.sheets("Information_IntellProp").delete
application.displayalerts = true
on error goto 0
end with

'your code to add the sheets.

===========

But you could check...

if worksheetexists("Information_Database_Searches", activeworkbook) then
'do nothing
else
application.displayalerts = false
.sheets("Information_Database_Searches").delete
application.displayalerts = true
end if

.....



At the top/bottom of your module:
Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0)
End Function

juergenkemeter wrote:

Hi,
in the beginning of my code, several worksheets are being created:
ActiveWorkbook.Sheets.Add.Name = "Information_Database_Searches"
ActiveWorkbook.Sheets.Add.Name = "Information_Holdings"
ActiveWorkbook.Sheets.Add.Name = "Information_AgR_Publications"
ActiveWorkbook.Sheets.Add.Name = "Information_Pres_Reports"
ActiveWorkbook.Sheets.Add.Name = "Information_IntellProp"

For testing, I want to re-run the macro, but I always have to delete
these already created sheets so that the macro runs properly.

How can I write VBA, which first looks of these sheets are already
there. If not - create them, if yes, then go on with next section of
code.

Cheers
Juergen

--
juergenkemeter
------------------------------------------------------------------------
juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248
View this thread: http://www.excelforum.com/showthread...hreadid=500888

--

Dave Peterson

try [code] next
 

Here is a function that tells you if the sheet exists

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ActiveWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

So you would have something like

if sheetexists("Information_Database_Searches") = false then _
ActiveWorkbook.Sheets.Add.Name = "Information_Database_Searches"

--
HTH...

Jim Thomlinson


"juergenkemeter" wrote:


Hi,
in the beginning of my code, several worksheets are being created:
ActiveWorkbook.Sheets.Add.Name = "Information_Database_Searches"
ActiveWorkbook.Sheets.Add.Name = "Information_Holdings"
ActiveWorkbook.Sheets.Add.Name = "Information_AgR_Publications"
ActiveWorkbook.Sheets.Add.Name = "Information_Pres_Reports"
ActiveWorkbook.Sheets.Add.Name = "Information_IntellProp"

For testing, I want to re-run the macro, but I always have to delete
these already created sheets so that the macro runs properly.

How can I write VBA, which first looks of these sheets are already
there. If not - create them, if yes, then go on with next section of
code.

Cheers
Juergen


--
juergenkemeter
------------------------------------------------------------------------
juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248
View this thread: http://www.excelforum.com/showthread...hreadid=500888