Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
How to make a button VBA code reference other VBA code subroutines??? | Excel Programming | |||
stubborn Excel crash when editing code with code, one solution | Excel Programming | |||
VBA code delete code but ask for password and unlock VBA protection | Excel Programming |