Try it like this:

'PumpNumber is the Public string variable. The purpose of the routine is
'just to activate the sheet so that I can enter information about the
'pump into it.

Dim PumpNumber As Long

Sub test()
For i = 5 To 10
PumpNumber = i
NewSheet
Next 'i
End Sub

'Checks for sheet PumpSheet and creates it if it does
'not exist.

Public Sub NewSheet()

'If error is created then continues
On Error Resume Next

'Tries to activate the sheet PumpSheet
Sheets(PumpNumber).Activate

'Creates the sheet PumpSheet
If Err < 0 Then
Set xSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
With xSheet
.Name = PumpNumber
'Enters header information
.Range("A1") = "Number"
.Range("B1") = "Hours"
.Range("C1") = "Date"
.Range("D1") = "Lugs"
End With

End If

'Restores normal error handling
On Error GoTo 0

End Sub

Regards

Trevor


"Calypsoblur" wrote in message
...

PumpNumber is the Public string variable. The purpose of the routine is
just to activate the sheet so that I can enter information about the
pump into it.



'Checks for sheet PumpSheet and creates it if it does
'not exist.

Public Sub NewSheet()

'If error is created then continues
On Error Resume Next

'Tries to activate the sheet PumpSheet
Sheets(PumpNumber).Activate

'Creates the sheet PumpSheet
If Err < 0 Then
Sheets.Add
ActiveSheets.Name = PumpNumber
'Enters header information
Range("A1") = "Number"
Range("B1") = "Hours"
Range("C1") = "Date"
Range("D1") = "Lugs"

End If

'Restores normal error handling
On Error GoTo 0

End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/