Finding Worksheets

[DreamerZ]

I'm trying to write code that searches a workbook for a worksheet. The worksheet name is a date (9-30-02). If the worksheet is found, it is activated. If it's not found, it is created.

I can do the .Activate and .Add code. I'm getting a Run-Time error 9 (Subscript out of range) error if the sheet isn't found. How do I suppress that error and do the .Add?

Thoughts?

DreamerZ

 

[DaleWatson123321]

Hi DreamerZ,

I just created and ran the following routine without any problem.

Sub Check_Add_Worksheet()
shtName = "9-30-02"
shtCnt = ActiveWorkbook.Sheets.Count
cnt = 0
For i = 1 To shtCnt
sht = Worksheets(i).Name
If sht = shtName Then
Worksheets(shtName).Select
cnt = 1
Exit Sub
End If
Next
If cnt = 0 Then
Worksheets.Add
ActiveSheet.Name = shtName
End If
End Sub

I hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DreamerZ]

Yeah, that worked fine. The name of the sheet is variable based on input by the user. Originally, the input cell was in date format. Excel won't allow a date format sheet name (mm/dd/yy). It doesn't do the slashes.

I did convert the date name to a string, but the cell still stores the slashes even if input with dashes (mm-dd-yy), so it won't work.

Anyway, unless there is another way, I've converted that cell to text, which then loses it's formatting, but if that's the only way for it to work...

Thx again,

DreamerZ

 

[DaleWatson123321]

DreamerZ,

The following routine should solve your date problem. It will allow you to leave the input date in its original date format, and allow you to convert it to TEXT in VBA (to "9-30-02" format) so you can use it for the sheet name.

Sub Get_Date()
inpdate = Range("inp&quot.Value
datetext = Application.WorksheetFunction.Text(inpdate, "m-dd-yy&quot
MsgBox datetext
End Sub

Note: The above routine requires that the user-input date cell be assigned the range name "inp".

I hope this helps. Please advise.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Craig0201]

Dreamer,

A simpler way to do this would be to add an error handler....

Craig