CHECK IF SHEET TAB IS PRESENT

[jdavis9]

How do I look and see if a sheet name exists?
In other words, I want to create a sheet named "SUMMARY" if it doesnt already exist?

if (the sheet is not there) then
Sheets.Add
ActiveSheet.Name = "summary"
else
(do the rest of the macro)

 

[lenze]

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Sheets
If ws.Name = "Summary" Then Exit Sub
Next
Sheets.Add
ActiveSheet.Name = "Summary"
'Rest of Code
End Sub


Edit: You probably need to change Exit Sub to a GoTo statement to continue your macro
This message was edited by lenze on 2002-10-08 17:30

 

[jdavis9]

i get an error in the

For Each ws In Sheets

section

 

[lenze]

I don't know. It runs fine for me. What message do you get? Have you checked for the reference in VBE to the Objects library?

 

[jdavis9]

I'm guessing I didnt give enough information.

I have several columns of data.
depending on the test which was performed i select the column header and run an macro looking for a change in the value by a specified percentage. The first time it is run it needs to create a worksheet named summary, the next time it is run I need to pass the create sheet command and only extract the data/ref I am looking for an copy it to the sheet "Summary". I have everything working fine except the skipping the create sheet part. The code is attached below.

Sub deviation_event()
'

Sheets.Add
ActiveSheet.Name = "summary"
Range("A1").Select
ActiveCell.FormulaR1C1 = "SUMMARY"
'

Sheets("DATA").Select
'
'
' Application.ScreenUpdating = False
'
'
Dim CH_VALUE, HI_EVENT, LO_EVENT, DEV_PERCENT, DEV_VALUE
'
'EVENT VALUE FOR CHANNEL
'
DEV_PERCENT = Range("[macros.xls]SETPOINTS!C6")

'
'
'
' FIRST CHANNEL
'
ActiveCell.Select 'STARTING POINT
'
CH_VALUE = ActiveCell.Offset(50, 0) 'READ CURRENT VALUE FROM DATA, SKIP DOWN 50 LINES TO REMOVE STARING NOISE
DEV_VALUE = CH_VALUE * DEV_PERCENT 'ESTABLISH DEVIATION LIMITS
HI_EVENT = CH_VALUE + DEV_VALUE 'SET HIGH LIMIT FOR DISPLAY ONLY
LO_EVENT = CH_VALUE - DEV_VALUE 'SET LOW LIMIT FOR DISPLAY ONLY
MsgBox "HI_EVENT = " & HI_EVENT & vbCrLf & "LO_EVENT = " & LO_EVENT
'
ActiveCell.Offset(50, 0).Select ' START LOOKING FOR EVENT
Do Until Abs(ActiveCell.Value - CH_VALUE) > DEV_VALUE Or ActiveCell.Value = vbNullString 'LOOP FOR EVENT / EVENT SETPOINT ( < 30 )
ActiveCell.Offset(1, 0).Select
Loop
If ActiveCell.Value <> vbNullString Then
ActiveCell.Offset(0, -1).Copy 'COPY THE TIME STAMP
Selection.End(xlUp).Select 'RETURN TO TOP OF PAGE
ActiveCell.Offset(-1, 0).Select '
ActiveSheet.Paste 'PASTE TIME OF EVENT ABOVE THE CHANNEL LABEL
ActiveCell.Offset(1, 0).Select 'SELECT THE CHANNEL LABEL
Range(Selection, Selection.End(xlUp)).Copy 'SELECT THE CHANNEL LABEL AND EVENT TIME
'
Sheets("SUMMARY").Select 'CHANGE TO SUMMARY SHEET
Application.Range("A1").Select 'STARTING POINT FOR EVENT LOG
Do Until ActiveCell.Value = vbNullString 'LOOP TO FIND EMPTY ROW
ActiveCell.Offset(1, 0).Select
Loop
'
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Input change by more than: " & DEV_VALUE

Else
Sheets("SUMMARY").Select 'CHANGE TO SUMMARY SHEET
Application.Range("A1").Select 'STARTING POINT FOR EVENT LOG
Do Until ActiveCell.Value = vbNullString 'LOOP TO FIND EMPTY ROW
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = "NO DATA"
End If


Range("A1").Select
Sheets("SUMMARY").Select

Range("A1").Select
MsgBox "EVENT DATA COMPLETE"

' Application.ScreenUpdating = True
End Sub

 

[jdavis9]

HERE IS WHAT I ENDED UP WITH, THANKS FOR THE HELP, HERE AND IN EMAIL.....

Sub deviation_event()

'
Dim inputdev, devnow
devnow = Range("[ITS.xls]SETPOINT_DEV!D5")
inputdev = InputBox(Msg, "Enter the deviation to find:" & vbCrLf & "CURRENT:" & devnow)
If inputdev = vbNullString Then

Else
Range("[ITS.xls]SETPOINT_DEV!D5") = inputdev / 100
End If

INSERTSHEET:

On Error GoTo bypass:

Dim countit

If countit = 1 Then
Sheets.Add
ActiveSheet.Name = "SUMMARY"
Else
Sheets("SUMMARY").Select
End If


bypass:
countit = countit + 1
If countit = 1 Then GoTo INSERTSHEET: