Select method of Worksheet class failed

Hi,

I have no idea how to write VBA but I do know how to do a key stroke macro to print and save it in a personal worksheet that opens when I open excel 2003. I have recorded a macro that prints a workbook that is emailed to me, it works for all 5 workbooks which are all different in the number of tabs in each workbook. I recorded a separate macro for each workbook because each one has to have the "fit to page" done to the tab before it can be printed. Each macro works fine except for 1, when I try to run the macro it makes no difference weather I use the short cut key assigned or run it from the macro window (alt+F8), it get this:

Run-time error '1004'; Select method of Worksheet class failed

I really don't understand too much about macros, I no how to unhide the personal macro workbook where this macro is at. When I look at the book I see a blank sheet but the macros are available. When I get the error I get the choice to DEBUG if I do that it opens a new screen for me; I guess to step through it. I am guessing that someone needs me to copy and paste the code here so you can see it? Or can you already guess what the problem is?

Thanks
Ken

It's hanging on line 8, the last line that reads : ActiveSheet.Next.Select .. that is the line that is high lighted. When the excel sheet opens up it is on the wrong tab,but it is always on the same wrong tab. So I started the recording and it begins with using Ctrl+ pg dn to move to the correct tab to begin the print, then after it prints the first sheet I have to move to the beginning tab to continue the print. There are 10 tabs. When I open the report it is always on tab 1 (the far left tab), I have to move it to tab 9 to begin the print then back to tab 1, then 2, then 3 untill I reach tab 9 which since it has already been printed I skip over and print tab 10, then tab back to tab1 end print.Now it would be easier if I could get the guys sending the report just to move tab 9 to the beginning but they are not going to do that so I just have to deal with it. Anyway the program hangs when I am doing the Ctrl+ pg dn to move the tab. I have 5 other spreadsheets just like this one some with as many as 15 tabs they all work fine, I am wonderif there is a hidden tab? I have looked by high lighting whole sheet and then going to unhide but it is not showing me anything hidden. So hopefully you can figure out what is wrong, In the mean time I am going to move the 9 tab to the biginning and just start the print from there and see if that changes things.

Thank you
Ken

Before you waist any time on this I think I found the problem, there are hidden sheets, there are 5 hidden sheets, so now I guess all I need to know is if I unhide those sheets and then build my macro so that it skips those sheets, it should run, right? The only reason I am asking this before doing it is that a a a a the boss is getting tired of me trying to run this printing program as I am wasting paper and we are short on paper this month, so do I need to try this first sounds logical to me, that if there are hidden sheets it would hang. I am assuming the program is trying to count how many times I moving through the tabs and then trying to identify the correct worksheet name. In reality I am moving 8 times which puts me on tab 9, but if you UNHIDE the 5 hidden sheets I am 5 sheets short of where I need to be and the worksheet name does not match what the script is looking for..... Is that what is happening?

Thanks
Ken

it stops at tab 8 which it should print and does but at that point it is suppose to skip 7 tabs and print the last tab, this is also where the hidden tabs begin, there are 4 hidden tabs, then a tab you can see then two more hidden tabs and then the last tab. Here is a list of tabs.

(H) represents hidden tab
tab1, tab1a, tab2, tab2a, tab3, tab3a, tab4, tab4a, tab5(H), tab5a(H), tab6(H), tab6a(H), tab7, tab8(H), tab9(H), tab10

The sheet opens up with tab1, ..... I want the print to start at tab7, then go back to tab1, tab1a, tab2, tab2a, tab3, tab3a, tab4, tab4a [this is where the hidden tabs begin and where the macro hangs, i want it to skip tabs 5, 5a, 6, 6a, 7, 8, 9,]print tab10 END Print

This macro runs fine if I open up all the hidden sheets and run it, it will skip right over the tabs I need it to skip print last tab and stop. If I leave the tabs hidden it will not run, it hangs after printing tab4a. So I am asking is there a way to make it print this without unhiding the hidden tabs? If not I guess I have to teach people how to hide and unhide.

I have attached the current script that works when all tabs are exposed, but does not work if they are hidden .... guess I could create a macro that opens the hidden tabs??? Thats not a bad idea then I don't have to explain hide and unhide, sounds silly but I am amazed at what can happen when you teach someone something then walk away so that is what I will do if there is not a simple way to make this work as one macro I will create two one to unhide and one to print, but first I would like to know if there is away to do it with one macro.

I put brackets where it hangs

Thank you
Ken

OK I had to think about it a bit because I don't write code, but I do know that you can right click the bottom left of the tabs and get an index of the sheets. I know this is index is an automatic index that excel makes of your workbook. I am guessing that I would insert Worksheets(9).Select just before all the print commands so with out you telling me let me go play for a few hours and see if I can get close to how this works. I guess what I really don't understand is that when I right click to get the index the hidden sheets still do not appear there either ..... I don't want to waste your time so let me go read tonight and then play with it and see if I can even get close to what you want me to do.