Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check whether a worksheet exists already
I need to check whether a worksheet with the name "all" already exists in my workbook. If so, I don't name another worksheet "all", otherwise I have to name it "all". Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check whether a worksheet exists already
try. Don't remember where I got it.
Sub AddSheetWithNameCheckIfExists() Dim WS As Worksheet Dim newSheetName As String newSheetName = Sheets(1).Range("A1") ' Substitute your range here For Each WS In Worksheets If WS.Name = newSheetName Or newSheetName = "" Or IsNumeric(newSheetName) Then MsgBox "Sheet already exists or name is invalid", vbInformation Exit Sub End If Next Sheets.Add Type:="Worksheet" With ActiveSheet .Move After:=Worksheets(Worksheets.Count) .Name = newSheetName End With End Sub Sub mynewsheets() 'Dim c As Range For Each c In Range("myrange") On Error Resume Next If Sheets.Name < c Then Sheets.Add.Name = c End If Next c End Sub -- Don Guillett SalesAid Software "clui" wrote in message ... I need to check whether a worksheet with the name "all" already exists in my workbook. If so, I don't name another worksheet "all", otherwise I have to name it "all". Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check whether a worksheet exists already
Clui,
Try something like this: Sub RenameToAll() Dim CurrSht As String Dim ws As Worksheet Dim AlreadySet As String Const SearchFor As String = "All" CurrSht = ActiveSheet.Name For Each ws In ThisWorkbook.Worksheets If UCase(ws.Name) = UCase(SearchFor) Then AlreadySet = ws.Name Exit For End If Next ws If Not AlreadySet = "" Then MsgBox "A sheet named [" & AlreadySet & "] already exists in the Workbook" Exit Sub Else Sheets(CurrSht).Name = "All" End If End Sub THis will set the current sheet name to "All" if another sheet in the workbook is not named "All". I have set it up so "All" is not case sensitive. Alex J "clui" wrote in message ... I need to check whether a worksheet with the name "all" already exists in my workbook. If so, I don't name another worksheet "all", otherwise I have to name it "all". Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
check if sheet exists | Excel Discussion (Misc queries) | |||
check if worksheet exists | Excel Worksheet Functions | |||
Check to see if cell value exists | Excel Programming | |||
check if sheet exists | Excel Programming | |||
check if worksheet exists | Excel Programming |