I was able to get it working for now. Tomorrow I will test its stability
by opening and closing it a few (hundred? :) and running several
numbers through it each time. My problem was two fold. One I couldn't
open sheets with that number. When my variable is a number, Excel tries
to open not sheet "number" but rather sheet number. The difference
being the name and the number of the sheet. So, I copied in your change
and also added one more variable. I changed my old public variable to a
long, and the new one a string. Set the string equal to the public
long, and try to open the string. Worked like a charm! New code listed
below
Craig Robson


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

Public Sub NewSheet()
Dim PumpSheetNumber As String
'If error is created, then goes to label CreatSheet
On Error Resume Next

PumpSheetNumber = PumpNumber

'Tries to activate the sheet PumpSheet
Sheets(PumpSheetNumber).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


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

Hi Craig,
Best if you stick to the thread and also not change the subject title.
You started a new thread which make it hard to follow a thread if
it is becomes two threads. Assume your question was answered
and that the code you posted is the corrected current version, and works.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Calypsoblur" wrote in message ...

I was able to get it working for now. Tomorrow I will test its stability
by opening and closing it a few (hundred? :) and running several
numbers through it each time. My problem was two fold. One I couldn't
open sheets with that number. When my variable is a number, Excel tries
to open not sheet "number" but rather sheet number. The difference
being the name and the number of the sheet. So, I copied in your change
and also added one more variable. I changed my old public variable to a
long, and the new one a string. Set the string equal to the public
long, and try to open the string. Worked like a charm! New code listed
below
Craig Robson


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

Public Sub NewSheet()
Dim PumpSheetNumber As String
'If error is created, then goes to label CreatSheet
On Error Resume Next

PumpSheetNumber = PumpNumber

'Tries to activate the sheet PumpSheet
Sheets(PumpSheetNumber).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


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