When you use:

sheets(13)

it's the 13 sheet counting from the far left.

If you rearrange your worksheets in the workbook, you may be surprised what
sheets(1) refers to.

You can always use the name:
worksheets("summ")
or
sheets("summ")

but if the user renames the sheet name, then your code will also fail.

Next time you're in the VBE, show the project explorer for you workbook.

Expand all those members.
Under Microsoft Objects, you'll see something like:
Sheet13 (Summ)

The thing in the ()'s is what the user sees. The stuff to the left (Sheet13) is
called the code name. This codename is much more difficult for the user to mess
up (not impossible, though).

You could use the code name in your code to make it more robust:
Sheet13.select
Sheet13.Range("a1").clearcontents
....



CWillis wrote:

Maybe this will make you guys wonder. I did what Dave said. I renamed the
problem sheet a new name (no longer "Summ"). Added a worksheet (became
worksheet 13). Named the new worksheet "Summ" and pasted in the data from
the old worksheet 12. Now the macro activates sheet 13 as long as I refer to
it by name, "Summ". Now, even though the sheet is numbered 13,
sheets(12).activate brings it up and sheets(13).activate is out of range. I
guess excel knows the sheets by order, not the number next to the sheet on
the macro page.

Regardless, problem solved for now. Thank you very much.

"Dave Peterson" wrote:

I don't have any idea what's causing the trouble, but if you copy all the data
(manually) from that sheet to a new sheet (include headers, footers, names,
filters, ... all that stuff), then toss that original sheet, can you select that
new sheet in code?



CWillis wrote:

I tried sheets() and worksheets(), .select and .activate, and "SheetName" and
the sheet number. All combinations work for sheets 1-11. None work for
sheet 12. Could I have something in the code that won't let it activate that
sheet. (I can click on the sheet to activate it. I recorded a macro of
that. It won't run that macro either.) Any ideas would be much appreciated.

"CLR" wrote:

I dunno......I always use

Sheets("SheetName").Select
or
WorkSheets("SheetName").Select

Maybe just try re-typing, and try another two sheets to see if it's
something with that one sheet, or something in the code.......

hth
Vaya con Dios,
Chuck, CABGx3




"CWillis" wrote:

The following code opens sheet1. When I uncomment sheet 12 and comment
sheet1, it gives the error: "Run-time error '57121': Application-defined or
object-defined error" Sheet12 exists. Any ideas? Thanks.

Sub testing()

Sheets(1).Select
'Sheets(12).Select

End Sub


--

Dave Peterson


--

Dave Peterson