Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find a number on another worksheet | Excel Worksheet Functions | |||
Finding the number of a worksheet | Excel Discussion (Misc queries) | |||
Enter number on a worksheet to copy row from another worksheet | Excel Worksheet Functions | |||
Basic Question - How do I return the worksheet number of the active worksheet? | Excel Programming | |||
Automatically number a worksheet | Excel Discussion (Misc queries) |