Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet activation
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet activation
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet activation
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet activation
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet activation
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet activation
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
lock tab in sheet 2 until cell in sheet one is completed | Excel Worksheet Functions | |||
Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) |