worksheet number OutOfRange
 

Hi!
I create a new workbook (VBScript in Excel 2003) and automatically 3 sheets
are created (Sheet1, Sheet2, Sheet3).
As I run thru the input file, if there's a new client, I want to use a new
Sheet.
Sheet 1 is successfully renamed to clientName (see code below), but when I
want to use the next one, Sheet2, I get an OutOfRange error.

worksheet = 1
Set objWorksheet = objWorkbook.Worksheets(worksheet)
objWorksheet.Name = clientName
lastClientName = clientName

If (clientName < lastClientName)
worksheet = worksheet + 1
Set objWorksheet = objWorkbook.Worksheets(worksheet)
objWorksheet.Name = clientName
End If

worksheet number OutOfRange
 

Some things...
1. "worksheet" is a name already used by Excel, so don't use it as a variable.
2. Your code shouldn't even compile as you are missing "then"at the end of the line...
"If (clientName < lastClientName)"
3. The following would not accomplish anything as clientname always equals lastclientname

lastClientName = clientName
If (clientName < lastClientName) Then
worksheet = worksheet + 1
Set objWorksheet = objWorkbook.Worksheets(worksheet)
objWorksheet.Name = clientName
End If
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"mechi"
wrote in message
Hi!
I create a new workbook (VBScript in Excel 2003) and automatically 3 sheets
are created (Sheet1, Sheet2, Sheet3).
As I run thru the input file, if there's a new client, I want to use a new
Sheet.
Sheet 1 is successfully renamed to clientName (see code below), but when I
want to use the next one, Sheet2, I get an OutOfRange error.

worksheet = 1
Set objWorksheet = objWorkbook.Worksheets(worksheet)
objWorksheet.Name = clientName
lastClientName = clientName

If (clientName < lastClientName)
worksheet = worksheet + 1
Set objWorksheet = objWorkbook.Worksheets(worksheet)
objWorksheet.Name = clientName
End If

worksheet number OutOfRange
 

Thanks!
The code does compile - I should have put ... to show that there's other
code in between.
I figures out the Range problem by adding new worksheets after worksheet=3

wsheetNum = wsheetNum + 1
If wsheetNum 3 Then
Set objWorksheet = objWorkbook.Worksheets.Add()
Else
Set objWorksheet = objWorkbook.Worksheets(wsheetNum)
End If

"Jim Cone" wrote:

Some things...
1. "worksheet" is a name already used by Excel, so don't use it as a variable.
2. Your code shouldn't even compile as you are missing "then"at the end of the line...
"If (clientName < lastClientName)"
3. The following would not accomplish anything as clientname always equals lastclientname

lastClientName = clientName
If (clientName < lastClientName) Then
worksheet = worksheet + 1
Set objWorksheet = objWorkbook.Worksheets(worksheet)
objWorksheet.Name = clientName
End If
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"mechi"
wrote in message
Hi!
I create a new workbook (VBScript in Excel 2003) and automatically 3 sheets
are created (Sheet1, Sheet2, Sheet3).
As I run thru the input file, if there's a new client, I want to use a new
Sheet.
Sheet 1 is successfully renamed to clientName (see code below), but when I
want to use the next one, Sheet2, I get an OutOfRange error.

worksheet = 1
Set objWorksheet = objWorkbook.Worksheets(worksheet)
objWorksheet.Name = clientName
lastClientName = clientName

If (clientName < lastClientName)
worksheet = worksheet + 1
Set objWorksheet = objWorkbook.Worksheets(worksheet)
objWorksheet.Name = clientName
End If