Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem finding named sheet
I have created a sheet using VBA. When I try to select the sheet, it doesn't
work, but if I try to name anew sheet with same name, an error occurs. This code was originally used to create sheet (Dept is a string) 'create new dept sheet Set NewSheet = Worksheets.Add NewSheet.Name = Dept This code is trying to test for presence of sheet, but testpagevalue does not pick up value in the existing sheet, so gives error? Private Function SheetExists(sname) As Boolean ' True if sheet exists in the active workbook and create if not present Dim testPageValue As Variant 'use to test for page presence 'test if destination sheet exists On Error Resume Next Err.Clear ' any cell will do testPageValue = Worksheets(sname).Range("A1").Value If Err.Number < 0 Then 'page does not exist, create it SheetExists = False Err.Clear On Error GoTo 0 Worksheets.Add ' add sheet, it gets selected 'can fail if destSheet is not a valid sheet name! ActiveSheet.Name = sname ' name it Else SheetExists = True End If End Function -- Patricia D |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem finding named sheet
Here is a sheet exists function that you can use...
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 = ThisWorkbook SheetExists = CBool(Len(Wb.Sheets(SName).Name)) End Function Note that it does not create the sheet if one does not exist. That should be done outside of the function within a sub procedure. Generally speaking (IMO) in VBA functions should only return values. They should not create sheets or other such side effects. If sheetsexists("dept") = false then 'create your sheet end if -- HTH... Jim Thomlinson "Patricia D" wrote: I have created a sheet using VBA. When I try to select the sheet, it doesn't work, but if I try to name anew sheet with same name, an error occurs. This code was originally used to create sheet (Dept is a string) 'create new dept sheet Set NewSheet = Worksheets.Add NewSheet.Name = Dept This code is trying to test for presence of sheet, but testpagevalue does not pick up value in the existing sheet, so gives error? Private Function SheetExists(sname) As Boolean ' True if sheet exists in the active workbook and create if not present Dim testPageValue As Variant 'use to test for page presence 'test if destination sheet exists On Error Resume Next Err.Clear ' any cell will do testPageValue = Worksheets(sname).Range("A1").Value If Err.Number < 0 Then 'page does not exist, create it SheetExists = False Err.Clear On Error GoTo 0 Worksheets.Add ' add sheet, it gets selected 'can fail if destSheet is not a valid sheet name! ActiveSheet.Name = sname ' name it Else SheetExists = True End If End Function -- Patricia D |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FINDING A NAMED TAB | Excel Discussion (Misc queries) | |||
Finding Named Field in Formulas | Excel Worksheet Functions | |||
Finding Info from sheet 1 and removing only those rows from sheet | Excel Discussion (Misc queries) | |||
Create a sheet name, won't let me, says already sheet named that | Excel Worksheet Functions | |||
referencing a sheet named in a cell then using data from that sheet | Excel Worksheet Functions |