+ Reply to Thread
Results 1 to 30 of 30

Loop Macro Excluding 1 specific sheet

  1. #1
    Registered User
    Join Date
    07-11-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    48

    Loop Macro Excluding 1 specific sheet

    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?

  2. #2
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: Loop Macro Excluding 1 specific sheet

    Do you have a sample file that you can upload?

    Also specify which is the print range.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Loop Macro Excluding 1 specific sheet

    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

  4. #4
    Registered User
    Join Date
    07-11-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Loop Macro Excluding 1 specific sheet

    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.

  5. #5
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: Loop Macro Excluding 1 specific sheet

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

  6. #6
    Registered User
    Join Date
    07-11-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Loop Macro Excluding 1 specific sheet

    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.
    Last edited by arlu1201; 07-11-2012 at 02:43 PM. Reason: Use code tags in future.

  7. #7
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Loop Macro Excluding 1 specific sheet

    Hi norrie007
    You can try this
    Please Login or Register  to view this content.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  8. #8
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Loop Macro Excluding 1 specific sheet

    Hi noorie,

    Try this and let me know

    Please Login or Register  to view this content.

  9. #9
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Loop Macro Excluding 1 specific sheet

    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

  10. #10
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Loop Macro Excluding 1 specific sheet

    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.

  11. #11
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Loop Macro Excluding 1 specific sheet

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

    Thanks a lot

  12. #12
    Registered User
    Join Date
    07-11-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Loop Macro Excluding 1 specific sheet

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

    Quote Originally Posted by fredlo2010 View Post
    Hi noorie,

    Try this and let me know

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    07-11-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Loop Macro Excluding 1 specific sheet

    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?

  14. #14
    Registered User
    Join Date
    07-11-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Loop Macro Excluding 1 specific sheet

    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.
    Last edited by arlu1201; 07-12-2012 at 09:42 AM. Reason: Use code tags in future.

  15. #15
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Loop Macro Excluding 1 specific sheet

    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.

  16. #16
    Registered User
    Join Date
    07-11-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Loop Macro Excluding 1 specific sheet

    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.

  17. #17
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Loop Macro Excluding 1 specific sheet

    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.

  18. #18
    Registered User
    Join Date
    07-11-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Loop Macro Excluding 1 specific sheet

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

  19. #19
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Loop Macro Excluding 1 specific sheet

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

  20. #20
    Registered User
    Join Date
    07-11-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Loop Macro Excluding 1 specific sheet

    Just did. Thanks again!

  21. #21
    Registered User
    Join Date
    07-11-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Loop Macro Excluding 1 specific sheet

    Quote Originally Posted by jaslake View Post
    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?

  22. #22
    Registered User
    Join Date
    07-11-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Loop Macro Excluding 1 specific sheet

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

  23. #23
    Registered User
    Join Date
    07-11-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Loop Macro Excluding 1 specific sheet

    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.

  24. #24
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Loop Macro Excluding 1 specific sheet

    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?

  25. #25
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,516

    Re: Loop Macro Excluding 1 specific sheet

    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
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  26. #26
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Loop Macro Excluding 1 specific sheet

    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

  27. #27
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Loop Macro Excluding 1 specific sheet

    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
    Attached Files Attached Files
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  28. #28
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Loop Macro Excluding 1 specific sheet

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

  29. #29
    Registered User
    Join Date
    07-11-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Loop Macro Excluding 1 specific sheet

    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.

  30. #30
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Loop Macro Excluding 1 specific sheet

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1