We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Print macro not working

Gary Cooper
Gary Cooper asked
on
High Priority
38 Views
Last Modified: 2020-05-28
I have 3 separate macro buttons for printing 3 different ranges. When it prints, it doesn't "fit to 1 page", but prints larger type over 3 pages. If there is a better way of doing this, I would like to know.

ExpertsExchange_PRINTING.xlsm

The code I have used for the first page is as follows:

Sub Sheet_SUMMARY_1to69()
 
  Application.ScreenUpdating = False
  
   Sheet1.Select
   
  With ActiveSheet.PageSetup
    .BottomMargin = Application.InchesToPoints(1#)
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .RightFooter = "&""Arial Black,Regular""Pg. 1"
  End With
    
    Range("A1:R69").PrintOut
    
 Application.ScreenUpdating = True
 
End Sub

Open in new window

Comment
Watch Question

KimputerIT Manager
CERTIFIED EXPERT

Commented:
Change it slightly to:

 Sub Sheet_SUMMARY_1to69()
 
  Application.ScreenUpdating = False
  
   Sheet1.Select
   ActiveSheet.PageSetup.PrintArea = "$A$1:$R$69"
   Application.PrintCommunication = False
  With ActiveSheet.PageSetup
    .BottomMargin = Application.InchesToPoints(1#)
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .RightFooter = "&""Arial Black,Regular""Pg. 1"
  End With
    
    Application.PrintCommunication = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    
 Application.ScreenUpdating = True
 
End Sub

Gary Cooperretired

Author

Commented:
Thanks for getting back to me on this one....I appreciate it very much.
This code seems to be working except for print size being too small because the print stops on the page, two thirds of the way down. I don't know why it doesn't print down to the bottom margin, unless somehow the margin is set incorrectly.

KimputerIT Manager
CERTIFIED EXPERT

Commented:
I saw that already (before I posted the code), and I'm not sure it's fixable (seems hard programmed how Excel decided to print).
John KorchokProduction Manager
CERTIFIED EXPERT

Commented:
In Kimputer's version, change this to True:
IgnorePrintAreas:=True

Gary Cooperretired

Author

Commented:
Hi John....thanks for your input
I changed the code to True as you said, and it now prints the whole spreadsheet really small, squeezing cells A1-R178 onto 1 page.
Do you see anything else that might work?

John KorchokProduction Manager
CERTIFIED EXPERT

Commented:
In your original question, you were asking how to print to 1 page. It sounds now like you have a new question, but I'm not clear what that is. "Anything else that might work" doesn't tell me what you see as a problem. Please start a new thread with the details.
Gary Cooperretired

Author

Commented:
Hi again
I originally said "I have 3 separate macro buttons for printing 3 different ranges. When it prints, it doesn't "fit to 1 page", but prints larger type over 3 pages. If there is a better way of doing this, I would like to know. "

I should have said that I have 3 separate macro buttons. Each button prints a specified range of cells. I would like each range of cells to print on their own individual page. There will, therefore, be 3 separate pages printed if all 3 macro buttons were to be clicked/activated, one at a time.

KimputerIT Manager
CERTIFIED EXPERT

Commented:
The solution should be:

 ActiveSheet.PageSetup.PrintArea = "$A$1:$I$69"

I'm pretty sure J to R probably never contain any data that needs to be printed.

If it works, you can easily adjust the other 2 buttons, as the code is the same, except other numbers. for start/end rows.

If you still intend to print through R, I'm still pretty sure, you won't have a solution for the small printing (pretty much hardcoded printing logic in Excel)
John KorchokProduction Manager
CERTIFIED EXPERT

Commented:
Thanks for the clarification.
Sub Sheet_SUMMARY_1to69()
    With Application
        .ScreenUpdating = False
        .PrintCommunication = False
    End With
    With Sheet1
        .PageSetup.PrintArea = "$A$1:$I$69"
        With .PageSetup
            .BottomMargin = Application.InchesToPoints(1#)
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .RightFooter = "&""Arial Black,Regular""Pg. 1"
        End With
    End With
    Application.PrintCommunication = True
    Sheet1.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    Application.ScreenUpdating = True
End Sub

Sub Sheet_SUMMARY_72to141()
    With Application
        .ScreenUpdating = False
        .PrintCommunication = False
    End With
    With Sheet1
        .PageSetup.PrintArea = "$A$72:$I$141"
        With .PageSetup
            .BottomMargin = Application.InchesToPoints(1#)
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .RightFooter = "&""Arial Black,Regular""Pg. 1"
        End With
    End With
    Application.PrintCommunication = True
    Sheet1.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    Application.ScreenUpdating = True
End Sub

Sub Sheet_SUMMARY_146to178()
    With Application
        .ScreenUpdating = False
        .PrintCommunication = False
    End With
    With Sheet1
        .PageSetup.PrintArea = "$A$146:$I$178"
        With .PageSetup
            .BottomMargin = Application.InchesToPoints(1#)
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .RightFooter = "&""Arial Black,Regular""Pg. 1"
        End With
    End With
    Application.PrintCommunication = True
    Sheet1.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    Application.ScreenUpdating = True
End Sub
Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.