Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create Worksheet BUT If It Already Exists...
I have the following:
Sub EXAMPLE() Dim 1WorkSheet As String 1WorkSheet = "Sheet One" Sheets.Add.Name = 1WorkSheet Worksheets(1WorkSheet).Visible = xlSheetHidden End Sub How do I check, before 1WorkSheet is created, that there is not already a sheet called "Sheet One". And if there is, use the existing sheet, first of all erasing data in it and carrying on as normal? Thanks! Dave |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create Worksheet BUT If It Already Exists...
Here's how I would go about it.
Public Sub EXAMPLE() Dim wsh As Excel.Worksheet Dim strName As String strName = "Sheet One" On Error Resume Next Set wsh = ThisWorkbook.Worksheets(strName) On Error GoTo 0 If wsh Is Nothing Then ' does not exist Set wsh = ThisWorkbook.Worksheets.Add wsh.Name = strName Else wsh.UsedRange.Clear End If wsh.Visible = xlSheetHidden End Sub On Oct 30, 5:55 pm, Dave wrote: I have the following: Sub EXAMPLE() Dim 1WorkSheet As String 1WorkSheet = "Sheet One" Sheets.Add.Name = 1WorkSheet Worksheets(1WorkSheet).Visible = xlSheetHidden End Sub How do I check, before 1WorkSheet is created, that there is not already a sheet called "Sheet One". And if there is, use the existing sheet, first of all erasing data in it and carrying on as normal? Thanks! Dave |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create Worksheet BUT If It Already Exists...
Adapt this to suit
Sub ifsheet() Application.DisplayAlerts = False Dim mySheet As String mySheet = "sheet7" If mySheet = "" Then Exit Sub On Error Resume Next If Sheets(mySheet) Is Nothing Then MsgBox "no" 'sheets.add Else MsgBox "Yes" 'do your thing 'sheets(mysheet).cells.clear'or? End If Application.DisplayAlerts = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Dave" wrote in message ... I have the following: Sub EXAMPLE() Dim 1WorkSheet As String 1WorkSheet = "Sheet One" Sheets.Add.Name = 1WorkSheet Worksheets(1WorkSheet).Visible = xlSheetHidden End Sub How do I check, before 1WorkSheet is created, that there is not already a sheet called "Sheet One". And if there is, use the existing sheet, first of all erasing data in it and carrying on as normal? Thanks! Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show it here, if it exists there | Excel Discussion (Misc queries) | |||
Can I create a worksheet menu to select each other worksheet | Excel Discussion (Misc queries) | |||
File Exists | Excel Discussion (Misc queries) | |||
check if worksheet exists | Excel Worksheet Functions | |||
Need help with a function I'm not sure exists | Excel Worksheet Functions |