LinkBack URL
About LinkBacks
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?
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?
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]
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
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.
You record the macro with all the settings you require. Post that code here and we can help you with the loops.
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"
Sub PAGESETUP1() ' ' PAGESETUP1 Macro For i = Worksheets.Count To 3 Step -1 Set ws = Worksheets(i) ws.Select Range("G:X").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "$G:$G" End With ActiveSheet.PageSetup.PrintArea = "$G:$X" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "&D - &T" .CenterFooter = "&Z&F" .RightFooter = "Page &P" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.25) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = False .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With DoEvents Next End Sub
Last edited by arlu1201; 07-11-2012 at 02:43 PM. Reason: Use code tags in future.
Hi norrie007
You can try this'Option Explicit Sub PAGESETUP1() ' ' PAGESETUP1 Macro For i = Worksheets.Count To 3 Step -1 Set ws = Worksheets(i) If Not ws.Name = "S-5000 Facilities" Then '<------ Insert Your Worksheet Name Here ws.Select Range("G:X").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "$G:$G" End With ActiveSheet.PageSetup.PrintArea = "$G:$X" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "&D - &T" .CenterFooter = "&Z&F" .RightFooter = "Page &P" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.25) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = False .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With DoEvents End If '<----don't forget this line of code Next End Sub
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.
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?
Hi noorie,
Try this and let me know
Sub PAGESETUP1() ' ' PAGESETUP1 Macro Dim i As Long Dim ws As Worksheet For i = Worksheets.Count To 3 Step -1 If Not Worksheets(i).Name = "S-5000 Facilities" Then Set ws = Worksheets(i) With ws.PageSetup .PrintTitleColumns = "$G:$G" .PrintArea = "$G:$X" .LeftFooter = "&D - &T" .CenterFooter = "&Z&F" .RightFooter = "Page &P" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.25) .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .Orientation = xlLandscape .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed .ScaleWithDocHeaderFooter = True End With DoEvents End If Next End Sub
This code didnt work. Meaning the tab I didnt want to apply the page setup on was not skipped. :/
Originally Posted by fredlo2010
Hi noorie,
Try this and let me know
Sub PAGESETUP1() ' ' PAGESETUP1 Macro Dim i As Long Dim ws As Worksheet For i = Worksheets.Count To 3 Step -1 If Not Worksheets(i).Name = "S-5000 Facilities" Then Set ws = Worksheets(i) With ws.PageSetup .PrintTitleColumns = "$G:$G" .PrintArea = "$G:$X" .LeftFooter = "&D - &T" .CenterFooter = "&Z&F" .RightFooter = "Page &P" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.25) .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .Orientation = xlLandscape .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed .ScaleWithDocHeaderFooter = True End With DoEvents End If Next End Sub
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:
Columns("G:X").Select Range("G12").Activate ActiveSheet.PageSetup.PrintArea = "$G:$X" ActiveWindow.View = xlPageBreakPreview ActiveWindow.SmallScroll Down:=45 With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "$G:$G" End With ActiveSheet.PageSetup.PrintArea = "$G:$X" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "&D - &T" .CenterFooter = "&Z&F" .RightFooter = "Page &P" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.25) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 2 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = False .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With ActiveSheet.ResetAllPageBreaks ' Set ActiveSheet.HPageBreaks(2).Location = Range("G72") ActiveWindow.SmallScroll Down:=-66 With ActiveSheet.PageSetup .PrintTitleRows = "$12:$21" .PrintTitleColumns = "$G:$G" End With ActiveSheet.PageSetup.PrintArea = "$G:$X" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "&D - &T" .CenterFooter = "&Z&F" .RightFooter = "Page &P" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.25) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 57 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = False .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With ActiveWindow.View = xlNormalView
Last edited by arlu1201; 07-12-2012 at 09:42 AM. Reason: Use code tags in future.
Hi Noorie
Since you didn't post the complete code you may need to play with this'Option Explicit Sub PAGESETUP1() ' ' PAGESETUP1 Macro For i = Worksheets.Count To 3 Step -1 Set ws = Worksheets(i) If Not ws.Name = "S-5000 Facilities" Then '<------ Insert Your Worksheet Name Here ws.Select Range("G:X").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "$G:$G" End With ActiveSheet.PageSetup.PrintArea = "$G:$X" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "&D - &T" .CenterFooter = "&Z&F" .RightFooter = "Page &P" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.25) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = False .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With DoEvents End If '<----don't forget this line of code Next Call PAGESETUP2 '<------- this line added End Sub Sub PAGESETUP2() Sheets("S-5000 Facilities").Activate Columns("G:X").Select Range("G12").Activate ActiveSheet.PageSetup.PrintArea = "$G:$X" ActiveWindow.View = xlPageBreakPreview ActiveWindow.SmallScroll Down:=45 With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "$G:$G" End With ActiveSheet.PageSetup.PrintArea = "$G:$X" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "&D - &T" .CenterFooter = "&Z&F" .RightFooter = "Page &P" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.25) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 2 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = False .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With ActiveSheet.ResetAllPageBreaks ' Set ActiveSheet.HPageBreaks(2).Location = Range("G72") ActiveWindow.SmallScroll Down:=-66 With ActiveSheet.PageSetup .PrintTitleRows = "$12:$21" .PrintTitleColumns = "$G:$G" End With ActiveSheet.PageSetup.PrintArea = "$G:$X" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "&D - &T" .CenterFooter = "&Z&F" .RightFooter = "Page &P" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.25) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 57 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = False .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With ActiveWindow.View = xlNormalView End Sub
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.
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
Hi fredlo2010
No, no reason...the code COULD USE rewriting but doesn't REQUIRE it...so I chose not to.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?
Thanks for the input jaslake. I just wanted to make sure everything was optional.
Thanks a lot
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.
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.Regarding thisOption Explicit Dim ws As Worksheet Sub PAGESETUP1() Dim i As Long Application.ScreenUpdating = False For i = Worksheets.Count To 3 Step -1 Set ws = Worksheets(i) If Not ws.Name = "S-5000 Facilities" Then With ws.PageSetup .PrintTitleColumns = "$G:$G" .PrintArea = "$G:$X" .LeftFooter = "&D - &T" .CenterFooter = "&Z&F" .RightFooter = "Page &P" .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .Orientation = xlLandscape .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed .ScaleWithDocHeaderFooter = True End With End If Next Call PAGESETUP2 Application.ScreenUpdating = True End Sub Sub PAGESETUP2() Sheets("S-5000 Facilities").Activate Set ws = ActiveSheet With ws .ResetAllPageBreaks .HPageBreaks(2).Location = Range("G72") End With With ws.PageSetup .PrintTitleRows = "$12:$21" .PrintTitleColumns = "$G:$G" .PrintArea = "$G:$X" .LeftFooter = "&D - &T" .CenterFooter = "&Z&F" .RightFooter = "Page &P" .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .Orientation = xlLandscape .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .FitToPagesWide = 1 .FitToPagesTall = 2 .PrintErrors = xlPrintErrorsDisplayed .ScaleWithDocHeaderFooter = True End With ActiveWindow.View = xlNormalView End SubThe same way you started this one...it's really quite simple...enter the Forum...click on +Post New Thread.How do I post a new thread?
Regarding thisNo clue...have you tried recording a Macro to do so?is there a way to do a global print range by selecting all tabs instead of creating a loop macro?
Just select all the sheets you want to have the same format and record the settings you want.
You'll get something like:
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "Testing - Page &P - Header" .RightHeader = "" .LeftFooter = "" .CenterFooter = "Testing - Page &P - Footer" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.905511811023622) .RightMargin = Application.InchesToPoints(0.905511811023622) .TopMargin = Application.InchesToPoints(0.94488188976378) .BottomMargin = Application.InchesToPoints(0.94488188976378) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 2 .FitToPagesTall = 2 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = True .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
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
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
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
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.
Awesome abousetta. Its great how we implement each other's ideas to make a piece of code better and better.
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.
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.
Now if your default are empty header, footer, right and left margins then this should suffice:.LeftHeader = "" .CenterHeader = "Testing - Page &P - Header" .RightHeader = "" .LeftFooter = "" .CenterFooter = "Testing - Page &P - Footer" .RightFooter = ""
Therefore, the run-time should be less..CenterHeader = "Testing - Page &P - Header" .CenterFooter = "Testing - Page &P - Footer"
Hope this helps.
abousetta
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks