Loop Macro Excluding 1 specific sheet

[noorie007]

I need help with a macro? I need a loop macro performning print setup on all sheets excluding 1 specific sheet. Can I get help please?

[arlu1201]

Do you have a sample file that you can upload?

Also specify which is the print range.

[fredlo2010]

Hi Noorie,

Welcome to the forum. The best way would be to record a macro with all the settings you want. Then that code can be modified to use variables and the loop. With the exception. It would be great if you could post a copy of the workbook as well.

Thanks

[noorie007]

I can record a macro to to the print range... that part i think i can work with. I'm having problem with excluding 1 sheet from the loop. The file is pretty large. Basically, its 40 tabs (40 PLs) all print on one page each except for 1PL that prints on 2 pages. So I have to accomodate that in the macro somehow.

[arlu1201]

You record the macro with all the settings you require. Post that code here and we can help you with the loops.

[noorie007]

This is a loop macro I have so far that goes through all pages. I need 1 Sheet excluded from this loop called "S-5000 Facilities"

Please Login or Register  to view this content.

[jaslake]

Hi norrie007
You can try this

Please Login or Register  to view this content.

[fredlo2010]

Hi noorie,

Try this and let me know

Please Login or Register  to view this content.

[fredlo2010]

Hi jaslake,

I deleted a lot of code the macro recorded. Things that were set to "" and false statements. I see you kept everything in your version. Is there a reason for that? I am new to VBA too

Thanks

[jaslake]

Hi fredlo2010

I deleted a lot of code the macro recorded. Things that were set to "" and false statements. I see you kept everything in your version. Is there a reason for that?

No, no reason...the code COULD USE rewriting but doesn't REQUIRE it...so I chose not to.

[fredlo2010]

Thanks for the input jaslake. I just wanted to make sure everything was optional.

Thanks a lot

[noorie007]

This code didnt work. Meaning the tab I didnt want to apply the page setup on was not skipped. :/

Hi noorie,

Try this and let me know

Please Login or Register  to view this content.

[noorie007]

Hi John, I tried this code and the loop is still taking tab: S-5000 Facilities and applying the page setup. I need to skip this tab. What should I do?

[noorie007]

Sorry Guys it actually worked. Thanks so much for your help. Both codes worked! I just had an extra - in the tab name. I have 1 last thing i need help with, is that can I possibly add page setup for Facilities in the same macro? So basically do the following for facilities tab and the rest is the loop above. Here is what I recorded for Facilities:

Please Login or Register  to view this content.

[jaslake]

Hi Noorie
Since you didn't post the complete code you may need to play with this

Please Login or Register  to view this content.

[noorie007]

Hi John,
How can I combine the 2 macros to be run at the same time? Right now I see them as Page setup1 and Page setup2.

[jaslake]

Hi Noorie
They will run at the same time by virtue of this line of code in PAGESETUP1

Please Login or Register  to view this content.

[noorie007]

Thank you so much John for all your help with this. It works fine I'll be taking a vba class in the fall.

[jaslake]

Hi Noorie
Glad I could help. If this satisfies your need please mark your Thread as SOLVED.

[noorie007]

Just did. Thanks again!

[noorie007]

Hi Noorie
Glad I could help. If this satisfies your need please mark your Thread as SOLVED.

Hi John,
The Macro is freezing up my computer. I think its too long. Im not sure what the problem is. Its running really slow. Can you please help me make it shorter maybe?

[noorie007]

is there a way to do a global print range by selecting all tabs instead of creating a loop macro?

[noorie007]

Can you please help me again? I need to shorten this macro since its really slow. Is it some how possible to select 39 tabs and perform print setup and do another print set up on the last tab? How do I post a new thread? I need help please.

[jaslake]

Hi norrie007
The Code can be shortened but you're doing a lot of formatting in VBA and that's a slow process (in VBA). This Code is shortened but I have no clue if it does as you require or if the process will be faster because I 'm not working with your files such that I can see what you expect.

Please Login or Register  to view this content.

Regarding this

How do I post a new thread?

The same way you started this one...it's really quite simple...enter the Forum...click on +Post New Thread.

Regarding this

is there a way to do a global print range by selecting all tabs instead of creating a loop macro?

No clue...have you tried recording a Macro to do so?

[TMS]

Just select all the sheets you want to have the same format and record the settings you want.

You'll get something like:

Please Login or Register  to view this content.

Regards, TMS

[fredlo2010]

Maybe I missed this part, but who's the final user? I have a slow macro too and there is no way for me to make it faster. Its just the way it is. What I did I used a message box to display that the macro was running and that i would take long...Somehow it helps me. After several months running that macro it doesn't seem so long.

Will all respect and all credits go to him, I took the original code created by jaslake and cleaned some of the select, activate. Also added the msgbox. Tell me if it helps.

Print Area.xlsm

[abousetta]

Here's an example using the example from post #26 with a progress bar to show you how long it's going to take to complete.

Hope this helps.

abousetta

[fredlo2010]

Awesome abousetta. Its great how we implement each other's ideas to make a piece of code better and better.

[noorie007]

Thanks all. There is no way to select all sheets and do a "set print area". Print options are limited when multiple sheets are selected.

[abousetta]

One thing you can do is only include in your code whatever is not standard to your Excel defaults. For example, the following code sets your header, footer, right and left margins.

Please Login or Register  to view this content.

Now if your default are empty header, footer, right and left margins then this should suffice:

Please Login or Register  to view this content.

Therefore, the run-time should be less.

Hope this helps.

abousetta