Hi

You can use the VBA level sheet names. Look in the project explorer window,
for each sheet you will see two sheet names:

Sheet1(MySheetName)

where Sheet1 is the VBA level sheet name and MySheetName is the visible
sheet name on the sheet tab, which you are changeing.

Using the first, you don't use the Worksheet(..) reference, just

Sheet1.Range("...")

Hopes this helps.

Regards,
Per


skrev i meddelelsen
...
Well, I figured it out. Thanks for your help. In the properties of
the worksheet, the (name) is "wksJobCostInput", but the name is "JC -
Tenant Alterations". It turns out when I do this:

Worksheets("wksJobCostInput").Range("valJobCostEnt ryList"), it can't
find the range, but when I do this:

Worksheets("JC - Tenant Alterations").Range("valJobCostEntryList")

it can find it no problem. That kind of sucks, because I plan on
changing the visible name at some point, and I will have to go into my
code and adjust all of the references. Does anyone know if this is a
known issue?

Michael

p.s. thanks for the heads up about spammers getting my e-mail
address. I am using google groups to post, and it doesn't seem to
allow me to change it. Well, I guess I'll just have to hope gmail
catches all of the spam.

On Jul 13, 5:55 pm, "Jim Cone" wrote:
Try changing the keys to...
Key1:=Range("valJobCostEntryList").Columns(1) 'whatever corresponds to
column E
and so on for Key2 and Key3

Note: Spammers harvest email addresses from newsgroups, I'm told.
--
Jim Cone
Portland, Oregon USA
(remove xxx)
(Free trial of Special Sort Excel add-in - two dozen ways to sort)


wrote in message
I am getting a "Subscript out of range" error trying to run a simple
bit of code. This is the code, essentially:

Range("valJobCostEntryList").Sort _
Key1:=Worksheets("wksJobCostInput").Range("E8"), _
Key2:=Worksheets("wksJobCostInput").Range("G8"), _
Key3:=Worksheets("wksJobCostInput").Range("H8")

I have tried including Worksheets("wksJobCostInput"). before Range,
and I have tried the following line of code:

Range("valJobCostEntryList").Select

which works just fine. I can't use select, however, because I am
capturing that Event to bring up a dialog box, and I don't want to
call the dialog box, just to sort the darned range!

Can anyone help me please?